SQL WHERE clause on a linked column

Developer Talk Scripts (Python and JS)
1

Setup: seatable cloud

Describe the Problem/Error/Question:

Hello and thank you for any help with the following. I am trying to filter some rows in a table based on the row_id. Through code I can ge the row_id but I haven’t been able to use it successfully in a WHERE clause.

I have a Table (Pedidos) which has a linked column name clientes. I want to get all rows where cliente equals a row_id = ‘123’.

I have tried WHERE cliente LIKE , WHERE cliente HAS ANY OF and keep getting empty object as a response.

Instead, if I input the value for the display_value (say Siraj), then I get the response I need. However, this isn’t a prefer solution since different clients could have the same name.

My question: linked columns are stored in the form of [{“row_id”: “123”, “display_value”: “abc”}], does seatable SQL engine only look into the display_value and can’t perform searches on the “row_id”?
2

Hi @sirajgb, and welcome to the seaTable forum!

You’re right, SQL engine only look into display_value for link columns. For you case, the best solution will probably be to create a rowID formula-type column in your linked table (the one containing clients) with the formula rowID() which ill allow you to display the row _id in this column. Then, in your Pedidos table, you can add a Link formula / Lookup column based on your clientes link column and displaying the content of the rowID column of your clients table.

Doing so, you’ll be able to use this new column containing the rows _id to filter your SQL queries.

Bests,
Benjamin

3

Thank you! Yes, that’s the approach I will take. I was leaning towards a getRows and then use filter but I’m worried about performance once the database becomes large (each order has 3-4 items).