how to do ilike or textSearch between multiple columns I wan

Question

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 · Answer

You should be able to chain ` ilike ` otherwise create a function inside Postgres and use ` rpc` to handle your search

YelloJello · Answer

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 · Answer

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

YelloJello · Answer

based on what I ve tried an equivalent sql query filters the data accurately though example ```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 ```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 · Answer

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 · Answer

FTS can get slow without the proper indexes btw make sure to use `EXPLAIN` liberally