Applying ilike to related entities

Hello, I've tried all kinds of variations so solve this but it didn't work. I have an express.js backend and I want to apply the ilike operator to filter a result set by values in sub-entities. Something like this:
let query = supabase
.from('shop_orders')
.select(


      *,
      line_items(*),
      order_shipments(*),
      shipping_address:addresses!shop_orders_shipping_address_id_fkey(*),
      billing_address:addresses!shop_orders_billing_address_id_fkey(*)


      *,
      line_items(*),
      order_shipments(*),
      shipping_address:addresses!shop_orders_shipping_address_id_fkey(*),
      billing_address:addresses!shop_orders_billing_address_id_fkey(*)


      *,
      line_items(*),
      order_shipments(*),
      shipping_address:addresses!shop_orders_shipping_address_id_fkey(*),
      billing_address:addresses!shop_orders_billing_address_id_fkey(*)


      *,
      line_items(*),
      order_shipments(*),
      shipping_address:addresses!shop_orders_shipping_address_id_fkey(*),
      billing_address:addresses!shop_orders_billing_address_id_fkey(*)

, { count: 'exact' })
.eq('user_id', userId)
.order('time_ordered', { ascending: false });

if (search) {
query = query.or(

line_items.title.ilike.%${search}%

line_items.title.ilike.%${search}%

line_items.title.ilike.%${search}%

line_items.title.ilike.%${search}%);
}

it always gives me an error, how do I write this? Help is very much appreciated

garyaustin•1/18/25, 2:20 PM

What is the purpose of your .or() with one filter in it?
Also with .or you have to use an option to specify the foreign table.

Ggaryaustin What is the purpose of your .or() with one filter in it? Also with .or you have ...

MaximOP•1/18/25, 4:50 PM

thanks, the second parameter seems to solve it. I haven't seen this anywhere that I searched. Initially the .or() included several columns that had to be searched, but now I wrote it like this, seems to work fine now if I haven't missed anything:

if (search) {
  query = query.ilike(

if (search) {
  query = query.ilike(

order_number, , %${search}%

);
  query = query.or(

);
  query = query.or(

title.ilike.%${search}%

, {referencedTable: 'line_items'});  
  query = query.or(

, {referencedTable: 'line_items'});  
  query = query.or(

name.ilike.%${search}%, {referencedTable: 'addresses'});, {referencedTable: 'addresses'});

garyaustin•1/18/25, 4:51 PM

.or() only does the "or" operation on filters inside the .or(filter1,filter2). Chaining .or is just anding those filters.

garyaustin•1/18/25, 4:51 PM

.or() also only works on one table.

MaximOP•1/18/25, 4:53 PM

okay, how do I do I a search like column LIKE 'text' OR relation.column LIKE 'text'

MaximOP•1/18/25, 4:53 PM

I tried it like this, but it didnt work either:
query.or(private_notes.ilike.%${search}%,private_notes.ilike.%${search}%,+
order_number.ilike.%${search}%order_number.ilike.%${search}%

line_items.title.ilike.%${search}%

line_items.title.ilike.%${search}%

+
shipping_address.name.ilike.%${search}%shipping_address.name.ilike.%${search}%+
shipping_address.street.ilike.%${search}%shipping_address.street.ilike.%${search}%
);

garyaustin•1/18/25, 4:54 PM

You can't. You'll need to use rpc to call a Postgres function and return rows from the table.

MaximOP•1/18/25, 4:55 PM

wow okay, I probably should have used mysql instead. but good to know

garyaustin•1/18/25, 4:56 PM

This is not a Postgres thing. If you use SQL it will work. It is a limitation of the REST API which does not attempt to do all possible SQL for performance and security reasons.

MaximOP•1/18/25, 4:57 PM

okay, thanks. Has to be an rpc then

garyaustin•1/18/25, 4:58 PM

I'm trying to find the hack that allows 2 tables to be dealt with. It may or may not work on 3. It is not documented so have to search for it.

garyaustin•1/18/25, 5:01 PM

https://postgrest.org/en/latest/references/api/resource_embedding.html#or-filtering-across-embedded-resources This is the way around it. It is in the raw URL format but does work with supabase-js. I've not used it and not sure it works for 3 tables.

PostgREST devel

Resource Embedding

PostgREST allows including related resources in a single API call. This reduces the need for many API requests. Foreign Key Joins: The server uses Foreign Keys to determine which database objects can be joined together. It supports joining tables, views and table-valued functions. For tables, it ...

MaximOP•1/18/25, 5:04 PM

thanks, I probably still have to use the rpc or just do the filtering in the backend after retrieving all records

Applying ilike to related entities

Similar Threads

Similar Threads

Similar Threads