S
Supabase•3y ago
Kcrik

Where clause on join not working, shouldn't return rows but still does

Hello guys, I have 2 tables with a join to link them together. typically Table 1 id name table_2_id Table 2 id name type In sql, if I wanted to return the row from table 1, where the type in table 2 is specific, I would just do: select * from table1 join table2 on table2.id = table1.table_2_id where table2.type = 'form_supa' I expect it to return nothing as the type is set as something else. In pure SQL,it works exactly as expected. With supabase client, I tried ton use the .match("table2.type", 'form_supa')...but all it does is just not return the matches between table 1 and table 2...but still return the table1 entries. How do you enforce the WHERE on join and inner join with the supabase client ? Thanks in advance for the help
7 Replies
jdgamble555
jdgamble555•3y ago
The supabase client really does inner joins with json. So your equivalent is: 
SELECT table1.*, to_json(t2) "type" FROM table1
INNER JOIN table2 t2 ON table1.table_2_id = t2.id
WHERE t2.type = 'form_supa'
SELECT table1.*, to_json(t2) "type" FROM table1
INNER JOIN table2 t2 ON table1.table_2_id = t2.id
WHERE t2.type = 'form_supa'
Would be something like this: 
supabase.from('table1').select('*, table_2_id!inner(*)').eq('table_2_id.type', 'form_supa')
supabase.from('table1').select('*, table_2_id!inner(*)').eq('table_2_id.type', 'form_supa')
or something similar J
Kcrik
KcrikOP•3y ago
@jdgamble555 , That was the issue. Supabase ignores convention of SQL and the join isn't an inner join by default. Very silly of Supabase, but it's sorted at least. Thanks for the help
garyaustin
garyaustin•3y ago
You need to use !inner in your select, but Discord may be eating some of your code you are showing.
Kcrik
KcrikOP•3y ago
@garyaustin , yes that was the issue. But as I mentioned, SQL default is inner, so it's very awkward Supabase choose to go against SQL default convention for joins....
jdgamble555
jdgamble555•3y ago
apparently they do it they way postgrest to be able to exclude top row items - https://postgrest.org/en/stable/releases/v9.0.0.html?highlight=inner%20join#resource-embedding-with-top-level-filtering - to me it makes more sense because it is like a graph
Kcrik
KcrikOP•3y ago
Hmm I disagree they do it postgrest, if I do a raw query in Supabase UI, it works as expected without any need for using inner join. So it looks more like the client or engine behind it , used via the nodejs client is not on part with default convention That's why I was very confused on why the raw query in Supabase UI works fine, but the conversion with Supabase client doesn't
garyaustin
garyaustin•3y ago
Don’t confuse PostgREST the database API Supabase uses and Postgres the database you are accessing directly with SQL in the UI. PostgREST.org.

Did you find this page helpful?