other-emerald•2y ago

Many-to-Many relationships

Just jumped in to Refine and I'm looking for a nice way to do many-to-many relationships with supabase as my data provider. - I've seen the useMany() example in the documentation, however that seems to illustrate a one-to-many relationship. I can forsee doing a many-to-many by creating two joins being quite ugly (with the join-table), so I was wondering if there's a Refine-way of doing this?

39 Replies

Omer•2y ago

Hey @.kyron 👋, Welcome, we are glad to see you! Could you check out this thread? I hope it's helpful for you ⚡️ https://discord.com/channels/837692625737613362/906206669787234334/1006198652873752616

other-emerald•2y ago

That looks like exactly what I'm looking for, thank you!

Omer•2y ago

Hooray! 🍻

other-emerald•2y ago

@Omer Now that I take a closer look (rather than excitedly proclaim this is for sure the solution.) - how do I get the value from one table in the filter without having it make one query per row? That's pretty inefficient, no? For example, for users <-> users_groups <-> groups, I'd expect one query like:

SELECT u.*, g.* FROM users u LEFT JOIN users_groups ug ON ug.user_id = u.id LEFT JOIN groups g ON g.id = ug.group_id

SELECT u.*, g.* FROM users u LEFT JOIN users_groups ug ON ug.user_id = u.id LEFT JOIN groups g ON g.id = ug.group_id

but unless there's some magic going on, I'm worried that this will result in one query for getting the users, then iterate through the results and get each users' groups with a call per row. (which is needlessly expensive)

Omer•2y ago

Oh I see. Maybe @cyborg31 can help @.kyron by the way, this thread might be of use to you 👀 https://discord.com/channels/837692625737613362/890159734907367496/1003954067074203731

yappiest-sapphire•2y ago

Yeah, that’s it. Shrinked to the most important information: If you filter based on a table from an inner join, you will need to use .select('*, mytable!inner(*)') within supabase. You can use inner joins and then also nest them. Example:

metaData: {
  select: "*, order!inner(*, shops!inner(*))",
},

metaData: {
  select: "*, order!inner(*, shops!inner(*))",
},

Or what you can do with an other example assuming that columns are called user and shop and the associated foreign tables shops and users. Example:

metaData: {
  select: "id, shop:shops(id, name, address), user:users(id, name), order_amount, order_date",
},

metaData: {
  select: "id, shop:shops(id, name, address), user:users(id, name), order_amount, order_date",
},

@Omer how come we always get in touch when I‘m on vacation. Or maybe I have too many vacations 😂

Omer•2y ago

Ohh sorry 😦 vacations are the perfect time to help the refine community. Just kidding hehe. Since we bothered you during the vacation, we will send you a gift card that you can use at the refine swag store 🎁 have a nice holiday 🏄‍♂️

other-emerald•2y ago

Thank you @cyborg31 and @Omer - I'll take this and run with it

yappiest-sapphire•2y ago

I‘m fine, always glad to help. Give it to the newcomers. 🙂 thanks, much appreciated

other-emerald•2y ago

@Omer The above worked btw! - A follow up question very much related - I'm trying to filter by said m2m relation in my table using useSelect() - It seems to be trying to filter by users.groups when postgrest needs to filter on groups.id to successfully achieve this. Is there a similar metaData option to set that? (I've tried a few things and referred to the docs, but I can't find any.)

Omer•2y ago

Cool. If i am not wrong you can use name path syntax for nested field filters ⚡️ setFilters({ field: "groups.id", value: 1, operator: "eq" });

Omer•2y ago

https://github.com/pankod/refine/blob/next/packages/supabase/src/index.ts#L146

GitHub

refine/index.ts at next · pankod/refine

Build your React-based CRUD applications, without constraints. - refine/index.ts at next · pankod/refine

other-emerald•2y ago

That's pretty much what I'd like, and it's good to know that the filters allow that - however getting them to work doesn't seem to be possible. Not sure if it's a bug, but it doesn't seem like this is even running (no console output):

    const {selectProps: groupSelectProps} = useSelect<IGroup>({
        resource: "groups",
        onSearch: (value) => {
            console.log('test');
            const filters: CrudFilters = [
                {
                    field: 'groups.id',
                    operator: 'in',
                    value: value,
                }
            ];
            return filters;
        },
        optionLabel: "name",
    });

    const {selectProps: groupSelectProps} = useSelect<IGroup>({
        resource: "groups",
        onSearch: (value) => {
            console.log('test');
            const filters: CrudFilters = [
                {
                    field: 'groups.id',
                    operator: 'in',
                    value: value,
                }
            ];
            return filters;
        },
        optionLabel: "name",
    });

@Omer the only output I'm getting is the failed network requests and this:

{code: '42703', details: null, hint: null, message: 'column users.groups does not exist', statusCode: 42703}

{code: '42703', details: null, hint: null, message: 'column users.groups does not exist', statusCode: 42703}

Omer•2y ago

Hmm this can be helpful? https://supabase.com/docs/reference/javascript/select#filtering-with-inner-joins

Fetch data: select() | Supabase

Performs vertical filtering with SELECT.

other-emerald•2y ago

Unfortunately not :/ - Supabase is correct in that users.groups does not exist. As that example shows, it should be doing something like groups.id in the filter, but I can't override the default one refine uses w/ useSelect. The docs say to use the onSearch property, but it doesn't seem to be running