Handling complex requests in Airtable

[srush]

I have a table with two of the fields called msgId and ID. msgId is generated by my app, whereas the ID is the auto number assigned to the record by Airtable. In my request (via the JS client library), I’d like to request data the following way:

Lookup a record with a given msgId and find the ID of that record, let’s call this required ID. I’d then like to sort the table by IDs desc and retrieve five records just before the matched record, i.e having the ID less than the required ID I found initially.

I’m thinking of two separate requests here, first get the ID related to the msgId by doing a filter by formula search function. Then send a second request, retrieve five records less than the ID. Is there any way to do this in a single request?

[Bill_French]

Hi srush, and welcome to the community!

srush:

I’m thinking of two separate requests here, first get the ID related to the msgId by doing a filter by formula search function.

Are the message IDs sequential or patterned in a manner that would allow blockification of the returned results? (i.e. a formula that looks at a subset of the message IDs) If not, the only way you might be able to do this with a single query is by using the date stamp. (i.e., if you know when the sought-after message ID occurred, a presorted list based on date stamp would allow you to use the maxRecords parameter to get six records starting with your message ID sorted DESC by date.

[srush]

Hey @Bill.French , thanks for the quick reply. How do I get six records starting from the particular record, the message ID of which is of interest to me? Let’s say I have records with message IDs ‘abc’, ‘123’, ‘pqr’, ‘567’ and I presort the records using created time. How do I get two records before the record identified by the msg Id ‘pqr’, giving me the records ‘123’ and ‘abc’?

To answer the first question, the message IDs are not sequential, they are just random but unique ids serving as the primary key for all the records.

[Bill_French]

srush:

How do I get six records starting from the particular record, the message ID of which is of interest to me?

I do not know. It’s very difficult to advise beyond a basic strategy without seeing and pondering the data set.

srush:

the message IDs are not sequential, they are just random but unique ids serving as the primary key for all the records.

If this is the case, you will not be able to blockify the query to capture only the desired records without first sorting them in a fashion that places then “nearby” the target message ID.

I also have no understanding of scale or velocity of the data occurring in the data set, nor any indication of why the query request via the API needs to be optimized to a single call. I suspect you have good reason for asking, but one answer – lacking deep insight into the data – is to make a single call and get all records; then use sorted array handling to ferret out the needed records.

Another idea - but again, it lacks deep knowledge about your data - is to index these “nearby” records as the data is created. Imagine a single field in each record that contains a small JSON payload containing the key field values for related records that this query process requires. This would allow you to fetch a single record based on message ID and instantly possess the nearby records/key fields for whatever it is you are trying to solve.

Lastly, there’s GraphQL, a technology designed to create a new API abstraction so that a single query can return related records. Indeed, a non-trivial architecture, but I’m flying blond for the most part.