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

YelloJello

03/02/2022, 6:29 PM
Chaining ilike on foreign tables + potential issues with or()
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 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%'
Chaining seems to yield weird results with eq() too, chaining eq is equivalent to match() (I think?) but match is strict (as expected) but eq isn't
s

Steve

03/02/2022, 10:26 PM
Hm, could you try using
*
instead of
%
?
y

YelloJello

03/02/2022, 10:30 PM
Haha, I was just about to write what solved my problem. I did try replacing
%
with
*
but what really did the trick for me was using an undocumented keyword called
inner
. Relevant issue: Related announcement:
This is still an issue though, but match does what I want. I ran eq() and match() against columns within the primary table, not embedded/foreign tables.