https://supabase.com/ logo
#help
Title
# help
a

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 + '%')
s

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.
y

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.
also, when you chain ilike via or() it doesn't seem to filter the data properly
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
a

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.
y

YelloJello

03/02/2022, 7:00 PM
FTS can get slow without the proper indexes btw, make sure to use
EXPLAIN
liberally