Supabase•3y ago•

2 replies

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 >'

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

collection_share

collection_share

is linked to

collection

collection

collection_id

collection_id

column with

on_cascade_delete

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\""
}

{
  "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)

    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)

    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

collections

instead of select few for

sharedWithId

sharedWithId

user

edit 3

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

I modified the code above using

!inner

!inner

    await supabase
    .from('collections')
    .select("*, items (*), collection_share!inner(shared_with, can_edit)")
    .eq("collection_share.shared_with", sharedWithId)

    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.

Supabase•3y ago•

2 replies

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 >'

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

collection_share

collection_share

is linked to

collection

collection

collection_id

collection_id

column with

on_cascade_delete

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\""
}

{
  "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)

    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)

    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

collections

instead of select few for

sharedWithId

sharedWithId

user

edit 3

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

I modified the code above using

!inner

!inner

    await supabase
    .from('collections')
    .select("*, items (*), collection_share!inner(shared_with, can_edit)")
    .eq("collection_share.shared_with", sharedWithId)

    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.

SQL to supabase orm

edit:

edit 2

edit 3

edit 4

SQL to supabase orm

edit:

edit 2

edit 3

edit 4

Similar Threads

Similar Threads

Similar Threads