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

[engnaveed]

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

1. Filtering using only load_number and pickup_city works fine:

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

2. Filtering using only dispatch_client_orgs.name also works:

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

Steps to Reproduce

1. 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
   );

2. 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}%
);
}

3. 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!

[laurenceisla]

I think this is pretty similar to this Discussion: #3333. The or query would be something like:

baseQuery
  .ilike('dispatch_client_orgs.name', `%${searchTerm}%`)
  .or(
    `load_number.ilike.%${searchTerm}%,pickup_city.ilike.%${searchTerm}%,dispatch_client_org_name.not.is.null`
  );

You may also need to add another empty embedding and use it for the filter instead (there's an example in the discussion too).

The PostgREST documentation has examples for these cases, although for two embedded resources.

[engnaveed]

Thank you for your reply! I checked the suggested discussion and the documentation thoroughly, but I’m still not getting the desired results.

The query you provided works well when the searchTerm is included in dispatch_client_orgs.name, such as when using searchTerm = 'alpha'. However, when I change the searchTerm to something that exists in other columns, like load_number or pickup_city, the query returns empty results, even though I know there are matching entries.

Could you please help me adjust the query so that it searches across all specified columns, regardless of which column contains the searchTerm? I want to ensure that the query returns results from any relevant column. Thank you!

[laurenceisla]

However, when I change the searchTerm to something that exists in other columns, like load_number or pickup_city, the query returns empty results, even though I know there are matching entries.

Ah, do you mean that, for example if searchTerm is present in load_number or pickup_city, but not in dispatch_client_orgs.name, then you get an empty dispatch_clients_orgs? Something like:

[
  {
    "id": "1000", 
    "inserted_at": "20240101",
    "dispatch_client_org_name": null,
    "..."
  }
]

If that's so, then you'll need to add a second embedding as I mentioned in the reference. An empty embed is done for filtering, and the other to retrieve data (the docs I linked explain why).

.select(
  `id, inserted_at,
   dco_filter:dispatch_client_orgs!inner(),
   dispatch_client_org_name:dispatch_client_orgs!inner(id, name, avatar),
   ...
  `
)
.ilike('dco_filter.name', `%${searchTerm}%`)
.or(
  `load_number.ilike.%${searchTerm}%,pickup_city.ilike.%${searchTerm}%,
   dco_filter.not.is.null`
)

The result should show then. If that's not it, then provide a JSON example of what you expect.

[engnaveed]

Yes, I think it only searches/filters on dispatch_client_org_name.name and is ignoring the or operator.

[engnaveed]

Thanks for your feedback! Here’s the updated query based on your suggestions:


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

if (searchTerm) {
    baseQuery
      .ilike('dco_filter.name', `%${searchTerm}%`)
      .or(
        `load_number.ilike.%${searchTerm}%, pickup_city.ilike.%${searchTerm}%, dco_filter.not.is.null`
      );
}

const { data, error } = await baseQuery;

if (error) {
    console.error("Error fetching data:", error);
} else {
    console.log("Fetched data:", data);
}

Issue Encountered

When using searchTerm = 'alpha', the query returns expected results as you can see
sample response with alpha searchTerm.json
. However, when changing searchTerm to '7821', it returns an empty array. I’ve checked the column names, and they seem correct

//Response

{
    "data": [],
    "count": 0
}

[laurenceisla]

Ah, I made a mistake here. It must be due to the !inner in the filter, it should be removed:

dco_filter:dispatch_client_orgs()

Check if that works.

[engnaveed]

**Thank you very much! **
The solution now works fine. Can you guide me further on the following scenario?

Is this correct?

dco_filter:dispatch_client_orgs() is for filtering, while dispatch_client_org_name:dispatch_client_orgs!inner(id, name, avatar) is used to get the actual data, right?

Question Will this syntax work for other tables as well? For example, user_profiles!driver(user_id, phone, name). First, I would use userdFilter:user_profiles!driver(), and then user_profiles!driver(user_id, phone, name) to retrieve the actual data.

In the baseQuery, it would look like this:

const baseQuery = supabase
      .select(
           `id, inserted_at,
           dco_filter:dispatch_client_orgs!inner(),
           dispatch_client_org_name:dispatch_client_orgs!inner(id, name, avatar),   
           userdFilter:user_profiles!driver(),
           user_profiles!driver(user_id, phone, name)
           ...
          `)

if(searchTerm){
        baseQuery
              .ilike('dco_filter.name', `%${searchTerm}%`)
              .ilike('userdFilter.name', `%${searchTerm}%`)
              .or(
                `load_number.ilike.%${searchTerm}%, pickup_city.ilike.%${searchTerm}%, dco_filter.not.is.null, userdFilter.not.is.null`
              );
        }

Thank you for your help!

[laurenceisla]

Question Will this syntax work for other tables as well? For example, user_profiles!driver(user_id, phone, name). First, I would use userdFilter:user_profiles!driver(), and then user_profiles!driver(user_id, phone, name) to retrieve the actual data.

Yes, correct! Remember to remove the !inner from the filters for these cases.