Supabase•4y ago

How to query data using a join table?

The official doc doesn't provide any examples in querying using a join table: https://supabase.com/docs/reference/javascript/select#query-foreign-tables So I have two tables product and user which have many-to-many mapping relationship, and I have a third join table product_user which has three fields: id, product_id and user_id. How do I get all products that belong to a certain user in javascript? I found the same issue here: https://github.com/supabase/supabase/discussions/1080 It says it was a limitation in 2021 and we can only do client-side filtering, what about now?

GitHub

Filtering on many-to-many relationship with join table · Discussion...

Hey folks! I have a question related to this discussion here and figured I'd break it out into it's own post. Basically, I'm using @kiwicopple 's suggestion to creat...

Fetch data: select() | Supabase

Performs vertical filtering with SELECT.

7 Replies

Twisted Chaz•4y ago

I'm also having an issue with this. With the schema above. I want to list all genres using the joining table, but can't figure out how to get it to work. And using GraphQL produces so much extra data. I'm thinking about creating a View to query instead

Ryann•4y ago

You can filter globally using a policy I have a query where I have a table called contact_link which has two contact_id's set on it a child_id and parent_id, where I inner join then as follows 'children' is what I want to name the field, 'contact_link' is the table, and 'parent_id' is the foreign key. I then can populate the foreign key of the child_id

    children:contact_link!parent_id(
      id,
      contact_type, 
      contact:child_id(id, full_name, profile_image)
    ),
    parent:contact_link!child_id(
      id,
      contact_type,
      contact:parent_id(id, full_name, profile_image)
    )

    children:contact_link!parent_id(
      id,
      contact_type, 
      contact:child_id(id, full_name, profile_image)
    ),
    parent:contact_link!child_id(
      id,
      contact_type,
      contact:parent_id(id, full_name, profile_image)
    )

garyaustin•4y ago

If you have a third joining table the foreign keys need to be part of the primary key. So in the dashboard table UI check primary key on them. This was a change a couple of months ago in PostgREST.

Ryann•4y ago

Do you mean the foreign key must link back to a primary key?

garyaustin•4y ago

No. If this is a join table with two foreign keys pointing to two other tables primary (usually) keys then they need to both be marked primary (that includes them in the tables overall primary key (has multiple columns). PostgREST needs this for performance reasons and that is all it is for (it won't look if they are not).
This is one discussion on it... https://github.com/supabase/supabase/discussions/7651 I'm not sure the ops tables follow this. Looks like they have a lot of double links between tables, which seems painful. The album_genres table appears to be a joining table with two fk's pointing to two other tables. But there are also links point back which means lots of extra updates.

GitHub

I can't seem to get many to many join query working · Discussion #7...

I have 2 main tables and a join table A car can have many features and a feature can have many cars.. hence the many to many. cars id(uuid), name features id(uuid), name car_features (Join table) i...

Ryann•4y ago

Ok thanks for the info, Will keep note.

laubonghaudoiOP•4y ago

So you mean there will be three primary keys in the join table? Wow it works like a charm. Yes you're right, so my join table product_user needs to check all three keys id, product_id and user_id as primary keys. Then I can query a user with products like this

await supabase.from("users").select(`
        *,
        products(*)
      `);

await supabase.from("users").select(`
        *,
        products(*)
      `);

This should be made clear in the doc, could have save us so much time

Gaming

Programming

How to query data using a join table?

Did you find this page helpful?