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


Terms & Conditions