SupabaseS
Supabase•3y ago
Mr Void

SQL to supabase orm

How can I convert the following query to supabase's ORM ?

SELECT * FROM collections
INNER JOIN collection_share
ON collections.id = collection_share.collection_id
WHERE collection_share.shared_with = '< user id >'


collection_share is linked to collection by collection_id column with on_cascade_delete


edit:

Got the following error when trying out the code below:
{
  "code": "22P02",
  "details": null,
  "hint": null,
  "message": "invalid input syntax for type uuid: \"collection_share.collection_id\""
}


    await supabase
    .from('collections')
    .select("*, items (*), collection_share (shared_with, can_edit)")
    .filter("id", "eq", "collection_share.collection_id")
    .eq("collection_share.shared_with", sharedWithId)


edit 2

Now i have tried the following:
    await supabase
    .from('collections')
    .select("*, items (*), collection_share (shared_with, can_edit)")
    .eq("collection_share.shared_with", sharedWithId)


However, this gives unexpected results. Returns all collections instead of select few for sharedWithId user

edit 3

After reading:
https://supabase.com/blog/postgrest-9#resource-embedding-with-inner-joins

I modified the code above using !inner:
    await supabase
    .from('collections')
    .select("*, items (*), collection_share!inner(shared_with, can_edit)")
    .eq("collection_share.shared_with", sharedWithId)


Seems to be working now.

edit 4

I was wrong. It does not work. 😩
0 Results are fetched while there should have been 1 result.
Was this page helpful?