how to do ilike or textSearch between multiple columns I wan Supabase #help

how to do ilike or textSearch between multiple col...

ak4zh

03/02/2022, 6:23 AM

how to do ilike or textSearch between multiple columns. I want to search if a text exists in either of 3 columns. I tried following but it does not seem to work:

query.ilike('columnOne|columnTwo|columnThree', '%' + searchTerm + '%')

silentworks

03/02/2022, 8:49 AM

You should be able to chain

.ilike()

, otherwise create a function inside Postgres and use

.rpc

to handle your search.

YelloJello

03/02/2022, 6:17 PM

Just messed around with ilike myself, how would you apply ilike on a foreign table? or() has an option object with a property to specify foreign tables, but ilike doesn't.

YelloJello

03/02/2022, 6:17 PM

also, when you chain ilike via or() it doesn't seem to filter the data properly

YelloJello

03/02/2022, 6:27 PM

based on what I've tried an equivalent sql query filters the data accurately though example:

Copy code

js
select('*, foreign_table!table_alias:f_key_1 (prop1, prop2)').or('prop1.ilike.%somevalue%, prop2.ilike.%somevalue%', { foreignTable: 'foreign_table' })

Would yield inaccurate results, IMO almost always doesn't filter. While the equivalent SQL filters the table properly:

Copy code

sql
SELECT * FROM primary_table INNER JOIN foreign_table ON primary_table.f_key = foreign_table.id WHERE prop1 ILIKE '%somevalue%' OR prop2 ILIKE '%somevalue%'

EDIT: Moved to a new thread #948648279128834098

ak4zh

03/02/2022, 6:58 PM

I ended up creating a sql function with

to_tsquery

and

to_vector

it works perfect. Supabase does not support this use case directly via client.

YelloJello

03/02/2022, 7:00 PM

FTS can get slow without the proper indexes btw, make sure to use

EXPLAIN

liberally

Previous Next