API filter for link_to_table fields

Technical Help
, ,
#1

Hello All,

I am using the baserow.io .

Suppose I have two table “actors” and “movies”. The actors table has a fiels that lists all the movies the actor has played.

I want to get via API the list of actors who are not in any movie. According to the documentation, I should use this URL:

https://api.baserow.io/api/database/rows/table/<actors table id>/?user_field_names=true&filter__movies__empty

But it returns an empty result, even if I know there are actors without movies. Conversely, if I try to list actors who have played in at least one movie, the api returns all rows, even those “empty” (https://api.baserow.io/api/database/rows/table/<actors table id>/?user_field_names=true&filter__movies__not_empty).

E.g. given these actors:

[{'Movies': [{'id': 1, 'value': 'Blade Runner Final Cut'},
             {'id': 52, 'value': "Ender's Game"}],
  'Name': 'Harrison Ford'},
 {'Movies': [{'id': 1, 'value': 'Blade Runner Final Cut'}],
  'Name': 'Sean Young'},
 {'Movies': [],
  'Name': 'Camilla Belle'}]

With the empty filter I would have expected to get:

[{'Movies': [],
  'Name': 'Camilla Belle'}]

While I have now:

[]

Am I doing something wrong, or have I encountered a bug?

Thanks for your help!

#2

Hi,

Try to replace the field name movies in your query with the id of the field which you can find in the API docs:
https://api.baserow.io/api/database/rows/table/<actors table id>/?user_field_names=true&filter__field_<id of the field>__empty

#3

Hi @frederikdc,

I had tested it, both with and without user_field_names=true, but the result is still the same.

Thanks in any case for the suggestion!

#4

Can you check and double check if there are no typo’s in your querystring? My most common error is not combining single underscores for the field id with the double underscores for the filters correctly. So the final result should for example befilter__field_123456__empty

If everything looks fine and it still does not work, I would recommend to create the filter in the grid view and check your browser inspector.

I have a video on my channel that demonstrates this principle: Use your browser inspector to detect API endpoints and payload - YouTube

#5

I am pretty sure there are no typos, because otherwise even the not_empty filter wouldn’t have worked, while as said it returns all rows.

On the other hand, I will surely test the url and API as you have suggested. And report here the results to help others who may have my same problem.

#6

I made another test: I created another row with that field empty. And only this row is correctly filtered. Somehow, the rows I was expected to filter are not filtered. There probably is something in how the data is stored that makes some rows not visible. The bad thing is that I do not remember how I created the original rows.

If needed I can share the original table data (just let me know how, because I see no attachment button).

AndBTW, I must say that what also the web interface has the same behaviour of the API, i.e. when I filter for the empty field the original rows are not visible, but the last I created yesterday is.