Skip to main content

I have a table of products with ~5000 records. I’d like to grab a subset of these records (about 1000 of them) using the API, and I already have the list of Record IDs that I want.


I’ve figured out two ways to get this data using filterByFormula, but both methods take a long time to fetch 1000 records.


First method: takes about 20 seconds


OR(
RECORD_ID = "a1b2c3",
RECORD_ID = "a1b2c4",
RECORD_ID = "a1b2c5",
RECORD_ID = "a1b2c6"
)

Second method: takes about 10 seconds


SEARCH(RECORD_ID(), "a1b2c3,a1b2c4,a1b2c5,a1b2c6") != ""

Are there any more efficient formulas I could be using here?

I hadn’t thought of using SEARCH before! I gave it a shot on 100 record ID’s and compared it to the OR method but didn’t see any difference (I still got results in ~500ms). I was limiting the returned fields to a single one for simplicity.


I’m curious what code (language/library?) you’re using and how you handle the paging (100 record limit and offset) from the API?


My hunch is that if you’re providing ~1000 record ID’s in your query and then stepping through the paged results, you’d be better off chunking your record ID’s into batches of 100 and making 10 individual requests. I don’t have time to test it now, but essentially that is how I handle large result sets in Airpress


Good question. I’m using this PHP client: https://github.com/sleiman/airtable-php


And I wrote my own helper function for stepping through all of the paged results:


// Recursive version of Airtable PHP client's getContent() method
// Including some built-in friendly debugging
function getContentRecursive($db_inventory, $table_name, $filters = =]) {

// Fetch the first response
$response = $db_inventory->getContent($table_name, $filters)->getResponse();

// If there's an error, show it and return an empty array.
if ($response->error) {
var_dump($response->error->type.': '.$response->error->message);
return n];
}

$content = =];
$content = array_merge($content, $responses'records']);
while ($response->next()) {
$response = $response->next()->getResponse();
$content = array_merge($content, $responses'records']);
}
return $content;
}

I will try chunking up the record IDs as you suggested – thanks for the tip!


Good question. I’m using this PHP client: https://github.com/sleiman/airtable-php


And I wrote my own helper function for stepping through all of the paged results:


// Recursive version of Airtable PHP client's getContent() method
// Including some built-in friendly debugging
function getContentRecursive($db_inventory, $table_name, $filters = =]) {

// Fetch the first response
$response = $db_inventory->getContent($table_name, $filters)->getResponse();

// If there's an error, show it and return an empty array.
if ($response->error) {
var_dump($response->error->type.': '.$response->error->message);
return n];
}

$content = =];
$content = array_merge($content, $responses'records']);
while ($response->next()) {
$response = $response->next()->getResponse();
$content = array_merge($content, $responses'records']);
}
return $content;
}

I will try chunking up the record IDs as you suggested – thanks for the tip!


Chunking the IDs into batches of 100 is indeed a second or two faster for my query. Here is the code snippet I’m using with the airtbale-php library:


    $product_records = r];
foreach (array_chunk(array_keys($products), 100) as $chunk) {
$response = $db_inventory->getContent('Products', c
'filterByFormula' => 'SEARCH(RECORD_ID(), "'.implode($chunk, ',').'") != ""'
])->getResponse()o'records'];
$product_records = array_merge($product_records, $response);
}

Reply