API: Retrieve multiple IDs (relationships)

carson.bird · May 17, 2023, 10:21pm

Please fill in the questionnaire below.

Technical Help Questionnaire

Have you read and followed the instructions at: *READ ME FIRST* Technical Help FAQs - #2 by nigel ?

Answer: Yes I have

Self-Hosted Installation and Setup Questions

Delete this section if you are using Baserow.io.

How have you self-hosted Baserow.

Hosted using Docker Compose. It’s running great

What are the specs of the service or server you are using to host Baserow.

Not relevant

Which version of Baserow are you using.

1.17.0

How have you configured your self-hosted installation?

Not relevant.

What commands if any did you use to start your Baserow server?

Not relevant.

Describe the problem

Can’t find a way to simply return multiple rows that have an ID in an array. Pseudoquery: Return rows with ID in [1, 3, 7].

Describe, step by step, how to reproduce the error or problem you are encountering.

I have a row, Zip Codes. Each zip code can be linked to multiple Geometries. I am trying to get each of those geometries in 1 API call, as running 50 API calls seems crazy. I know I can query the Zip Code row, and get a Geometry property like this: {...Geometries: [ { id: 1, value: 'A' }, { id: 2, value: 'B' }... ] }.

How do I use the API to get each of those Geometries from the Geometry table, without running a query for each Geometry?

How many rows in total do you have in your Baserow tables?

Right now, something like 45,000. Most tables are small. The Zip Code and geometry tables have tens of thousands of rows each.

Please attach full logs from all of Baserow’s services

Not relevant.

frederikdc · May 18, 2023, 5:17am

Hi,

I think the following structure with linked fields should solve the issue if I understand your question well.

There is a table with information about zip codes. This table requires a field (geo locations) that creates a link to the table with the Geometries. Use the type ‘Link to table’ for this. It allows you to connect multiple Geocentries to a ZIP code.

You notice that automatically a field was added to the Geometries table containing the ZIP code(s) that are attached to a location.

Now you can get all the rows (or filter them) from the ZIP code table with an API call to this endpoint: https://api.[your.instance.name]/api/database/rows/table/[your-table-id]/?user_field_names=true

This return the following result

As you notice, this field geo location is an array containing the id’s of all the geo locations that are linked to the ZIP code. You can work with ‘Lookup fields’ if you want more then just the id and a value.

Regards
Frederik

carson.bird · May 25, 2023, 10:55pm

Hmm, no, that’s not quite what I mean. I meant what I stated above - I’m looking to be able to access rows by their ID, just like you can do with the GET /{rowId} endpoint. I’m just looking to do that in a batch. It’s a very simple thing to do in SQL, especially through joins. Does Baserow have the functionality to request a list of rowIds in 1 API call? Also, does Baserow have the ability to retrieve an expanded version of related fields, i.e. all their columns, instead of just their ID and value?

frederikdc · May 26, 2023, 3:50pm

Can you clarify the issue with a couple of screenshots or how the SQL statement would look like?

Concerning the question about the related fields. You can expand them using lookup fields.

carson.bird · May 26, 2023, 6:56pm

Hey! Thanks so much for your help so far! The query would look like this:

SELECT * FROM Zip_Codes WHERE id IN (id1, id2, ..., idn)

Or something like:

SELECT * FROM Territories
JOIN Zip_Codes ON Zip_Codes.Territory_ID = Territories.id

(I always have to look up how to do SQL joins, so that syntax might be wrong. Hopefully it gets the idea across)

Without being able to do this through the Baserow API, the alternative is to do something like

// For every Zip Code in the territories list
for (const Zip_Code in Territories.Zip_Codes) {
  // Make a separate network request for that zip code
  fetch(`${baserowURL}/api/database/rows/table/${tableID}/${Zip_Code.id}/`);
}

This technically works, but we have territories with over 100 zip codes, and we’ll need to make multiple requests like this throughout the application. Ideally, there would be some way to run a network request like:

{
  url: `${baserowURL}/api/database/rows/table/${tableID}`,
  query: {
   `filter__id__in`: [id1, id2, idN],
  ...otherParams
  }
}

Any thoughts on how to do that? I’ve thought about something like:

{
  url: `${baserowURL}/api/database/rows/table/${tableID}/?filter_type=OR&filter__id__equal=${id1}&filter__id__equal=${id2}&filter__id__equal=${idN}`,
  query: {
    ...otherParams
  }
}

But I don’t think that doesn’t work because of the duplicate query param keys.

Any ideas?

frederikdc · May 30, 2023, 1:50pm

Hey, I think I now understand your problem and I agree it is hard to solve, but here is an attempt.

I created a table with information about the territories

ID is a formula: row_id()
ZIP_Codes is a linked field to the ZIP code tables
ZIP_City and ZIP_County are lookup fields

I also created a table for ZIP codes

Territory is a linked field
Territory_ID is a lookup field

Getting all the Territories results in the following JSON

So, I have a separate field for the zip code, city and county. I try to combine them with a formula field holding the following formula:

concat('"id": "', field('ZIP_Codes'),' ", city": 
 ',field('ZIP_City'),', "county: "', field('ZIP_County'))

The creates a kind of JSON string

Depending on your frontend, you can convert this string to a JSON object. Or you can use the map() function in JavaScript to finetune the API response.