First WHERE filter

Community Board
1

Is there a FIRST WHERE filter available via the API? I’ve done row, equals and I think it’s taking a long time because I have 250,000 rows and baserow is looking for all matches. I just need the first.

def get_matching_table_records(table_id, filter_field, filter_value, comparison_type='equal', order_by=None):
    """
    Fetch filtered records from a specific table in Baserow, with an option to specify the filter condition type and sorting.
    
    :param table_id: The ID of the table to query.
    :param filter_field: The field to filter by.
    :param filter_value: The value to filter by.
    :param condition_type: The type of condition to apply (e.g., 'equal', 'single_select_equal').
    :param order_by: Optional field or fields to sort by.
    :return: A list of records that match the filter criteria.
    """
    url = BASE_URL.format(table_id=table_id)
    filters_param = json.dumps({
        "filter_type": "AND",
        "filters": [{"type": comparison_type, "field": filter_field, "value": filter_value}],
        "groups": []
    })
    params = {
        'user_field_names': 'true',
        'filters': filters_param
    }

    if order_by:
        params['order_by'] = order_by

    records = fetch_paginated_data(url, params)
    return records
2

Hey @MarshallARoss,

Usually, specifying limit=1 to get only the first result should be enough, and the backend should be smart enough to take that limit into account when filtering the data. However, we would need more information about your use case to understand the issue.

For a grid view, you can see the available query parameters here: Baserow API spec

I’d have a few questions to understand the issue better:

  • how is your table structured? The number of fields, which field types, etc.
  • which filters are applied?
  • how much time is a long time?
3

Hi @davide

I’m in the API docs for my self-hosted table itself. I don’t see a limit parameter in there.

I have tons of songs - around 300,000. Each has a unique ID. I’m trying to fetch based on a known ID instead of a row number.

filter_params = {“filter_type”:“AND”,“filters”:[{“type”:“contains”,“field”:“song_f_id”,“value”:song_f_id}],“groups”:}

It can take 2-3 minutes to find a record sometimes.

4

Two to three minutes is definitely a long time, but I’m confident we can improve on that. Here are a couple of suggestions:

  • Have you considered using a search instead of a filter? Searches utilize indexed fields, which should speed up the process.

  • Alternatively, you could create a view sorted by the ID field and use the “equal” filter instead of “contains.” However, this might not be compatible with all field types and it will only work if you’re providing the exact song ID rather than a portion of it. Which type is the song ID field? The reason to create a sort by the song ID field is because Baserow automatically creates an index when possible to speed up the ordering, and I’m pretty sure Postgres will use the same index even for filtering if you try to filter only on the very same field, but I haven’t verified it yet, TBH

5

The song_f_id is a string and I’m looking for the one record that has a matching value using the API. I switched to equal and I added “order_by”: “song_f_id” to the query. It seems more responsive. I think the order_by indexing helped! Thank you