Best pratice for collections & many to many tables?

Ask us anything
1

Hi,

I’m trying to get a list “contacts”, each contact containing a list of “groups”, through a many-to-many table. I see that it’s not possible with collection’s “advanced” settings… as we can get another table’s value from a foreign key, but not using an M2M relationship.

So, I imagine, the best way in my case is to create a Database Function to call and receive a custom list ?
Is it a planned feature to support M2M ?

PS : btw I notice a limitation with FKs, in my case, “contacts” has an FK towards auth.users table, but writing “owner: owner_id(email)” says “Searched for a foreign key relationship between ‘contacts’ and ‘owner’ in the schema ‘public’, but no matches were found”, I guess it’s because contacts is in public, and users in auth… so it also requires a custom function !

Long live Weweb anyway, I trust you !

2

You need to make a contact table, with an id of auth user as a primary key, then in all your public schema tables, you need to set up references for contact.id not for auth.users.id, this way you’ll probably be able to make joins through advanced.

As for your question about intermediary (M2M) tables, you should be able to do something as follows…
Let’s say you have three tables:

Table contact {
id: int8
}

Table contact_detail {
contact_id: int8,
detail_id: int8
}

Table detail {
id: int8
}

You should be able to do the following in advanced mode of your collection and do as follows…
You’d select the contact as your primary table and then go to advanced like so:

id(detail_id(*))

This way you get your id foreign key table (which is in our case contact_detail and from contact_detail you get the detail_id and * (all) columns from the detail table, via the foreign key detail_id relationship. If you have your DB set up properly, this should work.

3

Thanks for your answer Roberto,
Unfortunately I don’t get i very well.

To clarify my post contains 2 distinct (yet, mixed in my case) questions :

  1. How to join my “contacts” collection to have the groups associated with each contact.

My schema is : contacts, m2m_contacts_groups, groups.
The FKs are m2m_contacts_groups.contact_id and m2m_contacts_groups.group_id.

I want my advanced collection like : [id, name,..., groups: [ {group_id, group_name}, ...] ]
I didn’t get how to achieve that in the manner you described with id(detail_id(*) ?

  1. How to get the [auth.]users.email value from the [public.]contact.owner_id attribute ? contact.owner_id being an FK from public to auth.users.id.
1 Like
4

for your case, you would select the contacts table in your collection and in andvanced do the following:

*,groups:id(group_id(*))

But I would also need to see the whole thing, maybe you could send the whole schema as it’s visualised in Supabase? It should work with joins, but I’m kind of guessing blindly - to get a query tailored to your needs I’d need the exact table column names (of the FKeys).

In my opinion this is a wrong approach and you should not do it like so as I mentioned - slighthly before. Check this out:

I feel like you might benefit from a 1:1 meeting regarding these corner cases and usage of Supabase “for your specific needs”. If you want, you can check out the link in my bio, I think it would be faster to have a chat about this :slight_smile: