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