Getting rows using filters, specifically link_row_has

Hi. The API documentation seems unfortunately lacking for basic usage. I have simliar goals as this poster How to search via API where I’d very much like to just retrieve some row or rows based on a criteria.

Some questions:

I can’t tell from the bug merge, but are user_field_names still broken/unimplemented?¹

Is it a known bug that trying to filter on a number, and giving a non-number, just returns everything, instead of properly nothing? (example: Numeric field, filter_field_1_equals=test returns all records instead of an error.)

Why are the field values starting at 200k+ instead of at 0 or 1, where it’d be easier and obvious?

But my biggest question is, how can someone use link_row_has???

Say I have two tables. Let’s call one ‘fruits’ and the other ‘type_details’.
Fruit has a row called ‘apples’ with some uninteresting columns, and a linked field to type_details. Type_details has a key field (a number I put in), stuff like name, weight, color, whatever, and the “fruit_type” that links back to apple. How can I run a query on my ‘type_details’ table to retrieve all records that refer to ‘apples’ in the other table? Or, if need be, conversely, if I get the row ‘apples’ from table ‘fruits’, how can I iterate the values in the link field to get those specific rows from ‘type_details’?

In caes my contrived example really sucks, I’m actually tracking a ‘thing’ that will have 5-10 related rows per. I want to run a query on the table with those 5-10 related rows and only return the ones that are linked to ‘thing’.

It’d really be great if the api docs provided some filter usage; it’s nice how they’re specific to the database and fields - that’s really very helpful already. There just doesn’t seem to be anything on this.
(I’m just doing this via curl, if that matters)

Thank you!

¹ Filter does not work

Hi @olly ,

I don’t have time and knowledge to answer everything so just to some of your questions:

I can’t tell from the bug merge, but are user_field_names still broken/unimplemented?¹

There are implemented, but not for all endpoints yet. The ones that are implemented should not have any specific bugs.

Is it a known bug that trying to filter on a number, and giving a non-number, just returns everything, instead of properly nothing? (example: Numeric field, filter_field_1_equals=test returns all records instead of an error.)

Yes, this is still an issue, we have a related bug report here: Text in a number field filter matches all rows (#775) · Issues · Bram Wiepjes / baserow · GitLab

Why are the field values starting at 200k+ instead of at 0 or 1, where it’d be easier and obvious?

Currently Baserow installs all templates right away, and those templates create tables and fields. This behavior might change in the future as we might make the template installation optional.

Hope that helps answering some of your questions!

Would this be solved by using a lookup field?

Thank you for answering some of my questions, though in hindsight, I should have probably only asked the single important one, that has me sitting on my hands: How do I use “link_row_has”?

I don’t know what you mean by using a lookup field: Isn’t that exactly what I’m already trying to do?

The field is linked to another table, but I can’t figure out how to use that field. Is there anywhere that just shows an example of how to use ‘link_row_has’ with a filter?

I do appreciate the other answers; it’s interesting to know about the templates causing the increased starting numbers, and I did miss that there was already a bug for the text in number field.

Since as a newb I may be making things too complicated, assume I have this:

Table: Stores (state, locations (linked->locations:location)
“Alaska”, “Anchorage, Fairbanks, Nome”, …, …, …
“Arizona”, “Phoenix, Flagstaff”, …, …, …

Table: Locations (Location, state (linked->stores:state, … )
“Anchorage”, “Alaska”, … , … , …
“Fairbanks”, “Alaska”, …, …, …
“Nome”, “Alaska”, …, …, …
“Phoenix”, “Arizona”, …, … ,…
“Flagstaff”, “Arizona”, …, …, …

My table has a “linked field” for state, in the locations table, and locations, in the state table. In the state table, if you look at locations, it has the names of the towns. In the locations table, looking at “state” is linked to the state table. (Obviously, this is contrived. My actual table has other fields, but for all intents and purposes, this is the format.)

Given that, how do I use a filter, with link_row_has, to find out if “state” (in locations) has a specific value (like “Alaska” or “Arizona”)? And the vice versa, if I know a state, how do I query my locations table to only return those? (It’s not an equals, because the linked row seems to be a nested array.)

Hopefully that helps with what I’m asking. I apologize for not asking my question better, as I’m new to this and I don’t know the correct terms or really how to ask. I look forward to any insight on what I need to do to use filter to accomplish this.

Apologies for late reply.

So do I understand the question well that you want to use “link_row_has” filter on the “link to table” field from the API?

Creating such filter manually in the interface reveals the filter definition (in the response of the request - see e.g. browser network tab):

{
  "id": 17895,
  "view": 40529,
  "field": 279222,
  "type": "link_row_has",
  "value": "4",
  "preload_values": {
    "display_name": "Test"
  }
}

So it seems that one has to use this type with a specific Id of the item from the linked table. So the filter can only filter on an existing options, providing the id as the value.

1 Like

Hello; thank you for taking the time to respond. From your post it made me think it really must be easier than I’ve encountered, and there is something stupid on my end. Such was was the case.

I was trying to do this:

curl -k -X GET -H "Authorization: Token REDACTEDDETCADER" "https://api.baserow.io/api/database/rows/table/xxxxx/?user_field_names=true&filter__field_282636_
_link_row_has=202201302319" | jq  .

and getting:
parse error: Invalid numeric literal at line 2, column 10

Which is weird, because that field is numeric, as far as I know. However, quoting it as

curl -k -X GET -H "Authorization: Token REDACTEDDETCADER" "https://api.baserow.io/api/database/rows/table/xxxxx/?user_field_names=true&filter__field_282636_
_link_row_has='202201302319'" | jq  .

works as expected, returning as nice as a json as I could want.

Always the little things and PEBCAK. :wink:
Thanks again!