Alternative for missing SQL Left Join functionality

Developer Talk API
1

Hey,

i have the following use case:
I have a table with linked records


{
  "sql": "SELECT * FROM FOOTABLE",
  "convert_keys": true
}

this will return

"results": [
    {
      "name": "foo",
      "bar": [
        {
          "row_id": "b0B6Z-UbRMa6YFHvar7U5w",
          "display_value": "foo"
        }
      ],
      "baz": [
        {
          "row_id": "IRZuGQyORkSl_mznoiBnug",
          "display_value": "baz"
        }
      ],
      "foobar": [
        {
          "row_id": "EgJNNpKxTnqRiccjGyR3WA",
          "display_value": "foobar"
        }
      ],

then some of those rows have more linked records which show up as:

"foo variants": [
"e-UEe04ySEeinrKGNrib1A",
"dLdUpBA3Q_Wdq_MhrJM2MQ",
"VnrUzy6KR4KNrqSUMRWhLg"
"YXgK041HTk-1pqi-ZohnEw",
"WUSab6wfSNC23y8HpNaT6g",
" BYtwQ4Y8QMC9ckq1ULSOwA" ,
" fNYj4gOfTey2KxUGekizrQ"|
],
"foo Ingredients": [
"bt2zrqI7SWyqT5ur6xzUBQ",
"YsnPb-XOS1Wbps7-bNIUGg",
" JErxddyoTrm15DMiJZlauA"
].

since left joins are not supported the only way to fetch the nested/linked data would be to make extra requests for each column id, which doesn’t really scale up since sometimes i would need more than 100 requests to get all the linked data i need.

I don’t see a solution in the api.
Am i overseeing something?

Is there a workaround for this?

Regards

2

List rows or sql-query only return max linked columns. If you want to see all links (>10) of a row, you need List Row Links. You can also provide multiple row_ids in one call.

Is this what you mean?

1 Like
3

Hi @cdb ,
As far as I understand, the question was more something like “is there any simple way with sql queries or any other API call to get the data of every linked rows without having to make one single query for each linked row ?”
@alej, do you confirm it’s actually what you meant ?

Bests,
Benjamin

1 Like
4

Hey @cdb,

Thanks for your reply.
Not sure how i would use that as a form of left join/foreign key.
Seems more like a filter :D?

Hi @bennhatton,

Precisely what i mean.

Bests,
Alej