Unable to Filter on Multiple Conditions Across Related Tables in Supabase Query

Issue Description

I am experiencing an issue when trying to filter results from the bookings table based on multiple conditions involving both direct columns and related table columns.

Related Discussions

I have tried various solutions, including discussions and issues from the following links: #787, #6778, #18997, PostgREST Issue #1075

   const baseQuery = supabase
     .from('bookings')
     .select(
       `id, inserted_at,
       dispatch_client_org_name:dispatch_client_orgs!inner(id, name, avatar), 
       dispatcher_name:user_profiles!dispatcher(user_id, name), dropoff_date`,
       { count: 'exact' }
     )
     .eq('dispatch_org_id', dispatch_org_id)
     .order('inserted_at', { ascending: false });

   if (searchTerm) {
     baseQuery.or(
       `load_number.ilike.%${searchTerm}%,pickup_city.ilike.%${searchTerm}%,dispatch_client_orgs.name.ilike.%${searchTerm}%`
     );
   }

Expected Behavior

I expect to retrieve records from the bookings table where:

Either load_number or pickup_city matches the searchTerm, or
The dispatch_client_orgs.name matches the searchTerm.

Actual Behavior

When attempting to combine these filters using the or method, I receive an error. Here’s the problematic code:

if (searchTerm) {
  baseQuery.or(
    `load_number.ilike.%${searchTerm}%,pickup_city.ilike.%${searchTerm}%,dispatch_client_orgs.name.ilike.%${searchTerm}%`
  );
}

This results in the following error:

code: 'PGRST100',
details: 'unexpected "a" expecting "not" or operator (eq, gt, ...)',
message: '"failed to parse logic tree ((or(load_number.ilike.%alpha%,pickup_city.ilike.%alpha%),or(dispatch_client_orgs_id.name.ilike.%alpha%)))" (line 1, column 87)'

//also try to add referenceTable but no luck

Working Conditions

Filtering using only load_number and pickup_city works fine:

if (searchTerm) {
 baseQuery.or(
   `load_number.ilike.%${searchTerm}%,pickup_city.ilike.%${searchTerm}%`
 );
}

Filtering using only dispatch_client_orgs.name also works:

if (searchTerm) {
 baseQuery.ilike('dispatch_client_orgs.name', `%${searchTerm}%`);
}

Steps to Reproduce

Set up the necessary database tables and relationships as defined below:


CREATE TABLE dispatch_client_orgs (
 id SERIAL PRIMARY KEY,
 name TEXT,
 avatar TEXT
);

CREATE TABLE bookings (
 id SERIAL PRIMARY KEY,
 dispatch_client_org_id INTEGER REFERENCES dispatch_client_orgs(id),
 load_number TEXT,
 pickup_city TEXT,
 dispatch_org_id UUID
);

Execute the following query: const baseQuery = supabase .from('bookings') .select( id, dispatch_client_org_name:dispatch_client_orgs!inner(id, name, avatar),, { count: 'exact' } ) .eq('dispatch_org_id', dispatch_org_id) .order('inserted_at', { ascending: false });

if (searchTerm) { baseQuery.or( load_number.ilike.%${searchTerm}%,pickup_city.ilike.%${searchTerm}%,dispatch_client_orgs.name.ilike.%${searchTerm}% ); }

Observe the error in the response.

code: 'PGRST100',
details: 'unexpected "a" expecting "not" or operator (eq, gt, ...)',
message: '"failed to parse logic tree ((or(load_number.ilike.%alpha%,pickup_city.ilike.%alpha%),or(dispatch_client_orgs_id.name.ilike.%alpha%)))" (line 1, column 87)'

Additional Information

I have attempted various solutions and consulted multiple resources, including Stack Overflow and the Supabase documentation, but none have resolved the issue. Any guidance or suggestions for a fix would be greatly appreciated!

PostgREST / postgrest