Combine multiple filters with logical/boolean operation

[EskelCz]

Is it possible to combine multiple front-end (js) filters?

For example I have columns age_from and age_to, which can be NULL.
I want to filter using lte and gte, but only when it's not NULL.

I know I can combine the two like this:

query.not('age_from', 'is', null).lte('age_from', $filter.who.age)

but that leads to two separate filters (filtering all rows with NULL, not just the ones with lower age). I need them joined into a single filter.
Something like:

query.not('age_from', 'is', null).and().lte('age_from', $filter.who.age)

I imagine it would be useful for many other things. Thanks

[GaryAustin1]

Several ways. You can use .or() with various logic combos in it and if you don't separate them with a comma then it will run as one big logic statement.

You can also do conditional filtering which is probably an easier way. With that method you just chain the filters based on their value being present to the query and then await the query to execute it.

https://supabase.com/docs/reference/javascript/using-filters?example=conditional-chaining

[GaryAustin1]

This gives examples of using logic in a single filter: https://postgrest.org/en/stable/references/api/tables_views.html?highlight=logic#logical-operators . It is in URL format, but should be pretty clear how to drop that in a string in a .or() filter.

[EskelCz]

I somehow missed this entry: https://supabase.com/docs/reference/javascript/or?example=use-or-with-and
I guess I was looking for a function called .and
It works now, thanks a lot.

query = query.or(`age_from.is.null,and(age_from.lte.${age})`)
query = query.or(`age_to.is.null,and(age_to.gte.${age})`)

[sighrobot]

This answer saved me; however the link is now dead and the information seems to have been removed from the Supabase docs. Fortunately there is an example in the PostgREST docs.

[RafaelCENG]

Hi im trying to do something similar but apparently im doing something wrong. I have a select query where I retrieve my data and some fields are using other tables.

I tried two approaches

.not("athlete_bike_id", "is", null)
.in("athlete_bikes.bike_types.name",values as Array<string>),

AND

 .or(`athlete_bike_id.neq.null,and(athlete_bikes.bike_types.name.in.(${values as Array<string>}))`,),

The first one is working half way. The .not is working and removes all my bikes with null ids. What happens next if my values = 'A' then my bikes_types with names B become null.

The second approach is not working at all I'm getting the following error

{
    "code": "PGRST100",
    "details": "unknown single value operator bike_types",
    "hint": null,
    "message": "\"failed to parse logic tree ((athlete_bike_id.neq.null,and(athlete_bikes.bike_types.name.in.(Road))))\" (line 1, column 58)"
}

[GaryAustin1]

You can't use multiple tables in an .or() filter. You can use one foreign table on the entire .or() if desired and the docs show how to do that.

[RafaelCENG]

Wow thanks for the instant reply. Im checking the docs I'm guessing Use 'or' with referenced tables.

Here is my select

"id,date,recording_name,total_distance,total_time,average_power,average_speed,ride_types!inner(name),provider,average_cadence,max_heartrate,max_power,elevation_gain,athlete_bikes(bike_name,bike_types(name)),average_pedal_balance_left",

To get the name of my bike I'm using two tables. I can't use !inner on the specific tables because then I'm losing all my sessions that do not have athlete_bikes.

[GaryAustin1]

You can use an alias on the same table in the .select(). One with !inner that you filter on and the other just the table.

[RafaelCENG]

Sorry to disturb again still trying to figure it out.
Here is my select with alias if I'm not mistaken.

 .select(      "id,date,recording_name,total_distance,total_time,average_power,average_speed,ride_types:ride_types!inner(name),provider,average_cadence,max_heartrate,max_power,elevation_gain,athlete_bikes:athlete_bikes!left(bike_name,bike_types:bike_types!left(name)),average_pedal_balance_left",
        { count: "exact" },
      ),

Here are my filters

currentBuilder
.not("athlete_bike_id", "is", null)
.in("athlete_bikes.bike_types.name",values as Array<string>, ),

When I apply the filter of Road here is an example of my first row

The problem is the below one should be TT and not included but after the filter it becomes null and its still included

Also tried something like this

.or(`athlete_bike_id.neq.null,athlete_bikes.bike_types.name.in.(${ values as Array<string>})`,{ foreignTable: "athlete_bikes" },)