flat-fuchsia•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

flat-fuchsia•2y ago

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

Omer•2y ago

Hooray! 🍻

flat-fuchsia•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

cloudy-cyan•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 🏄‍♂️

flat-fuchsia•2y ago

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

cloudy-cyan•2y ago

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

flat-fuchsia•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

flat-fuchsia•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.

flat-fuchsia•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

Omer•2y ago

It should have worked. So does your query work directly with supabaseClient? Maybe this can help us

flat-fuchsia•2y ago

It does work directly with supabaseClient, yes

Omer•2y ago

Cool, could you share?

flat-fuchsia•2y ago

so I did a quick test like this:

    (async () => {
        const { data } = await supabaseClient.from('users').select('id, name, groups!inner(*)').in('groups.id', ['94']);
        console.log(data);
    })();

    (async () => {
        const { data } = await supabaseClient.from('users').select('id, name, groups!inner(*)').in('groups.id', ['94']);
        console.log(data);
    })();

and got three results spitting out in my console

Omer•2y ago

Are you sending metaData correctly when using useSelect? https://github.com/pankod/refine/blob/next/packages/core/src/hooks/useSelect/index.ts#L138 https://github.com/pankod/refine/blob/next/packages/supabase/src/index.ts#L119

GitHub

refine/index.ts at next · pankod/refine

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

GitHub

refine/index.ts at next · pankod/refine

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

flat-fuchsia•2y ago

useSelect()'s resource is groups but the useTable() 's resource is users - what metaData / select would I be using for useSelect? shouldn't useSelect() just be applying a filter?

Omer•2y ago

I will make an example for you

flat-fuchsia•2y ago

(edit: I misread the first link.)

Omer•2y ago

I think I succeeded 👀 please visit create page and check posts select 🥁 https://stackblitz.com/edit/pankod-refine-o6vncg?file=src%2Fpages%2Fposts%2Fcreate.tsx%3AL30&preset=node

  const { selectProps: postsSelectProps } = useSelect<IPost>({
    resource: 'posts',
    onSearch: (value) => [
      {
        field: 'categories.id',
        operator: 'eq',
        value: value,
      },
    ],
    metaData: {
      select: '*, categories!inner(*)',
    },
  });

  const { selectProps: postsSelectProps } = useSelect<IPost>({
    resource: 'posts',
    onSearch: (value) => [
      {
        field: 'categories.id',
        operator: 'eq',
        value: value,
      },
    ],
    metaData: {
      select: '*, categories!inner(*)',
    },
  });

flat-fuchsia•2y ago

That's a one-to-many relation I believe 😅 I have tried to replicate nonetheless and unfortunately it's not working for my case

Omer•2y ago

ups 🙂 We're trying to build this query, right?

await supabaseClient.from('users').select('id, name, groups!inner(*)').in('groups.id', ['94']);

await supabaseClient.from('users').select('id, name, groups!inner(*)').in('groups.id', ['94']);

flat-fuchsia•2y ago

yep metaData in the useSelect context appears to be for the options in the filter btw in your example, say if, posts could have multiple categories. The table column would need a custom renderer, for instance:

                <Table.Column
                    dataIndex={'categories'}
                    title="Categories"
                    render={(categories: ICategory[]) => (<> {categories.map((category: ICategory) => category.title).join(', ')} </>)
                    filterDropdown={(props) => (
                        <FilterDropdown {...props}>
                            <Select
                                style={{minWidth: 200}}
                                mode="multiple"
                                placeholder="Select Category"
                                {...categorySelectProps}
                            />
                        </FilterDropdown>
                    )}
                />

                <Table.Column
                    dataIndex={'categories'}
                    title="Categories"
                    render={(categories: ICategory[]) => (<> {categories.map((category: ICategory) => category.title).join(', ')} </>)
                    filterDropdown={(props) => (
                        <FilterDropdown {...props}>
                            <Select
                                style={{minWidth: 200}}
                                mode="multiple"
                                placeholder="Select Category"
                                {...categorySelectProps}
                            />
                        </FilterDropdown>
                    )}
                />

Omer•2y ago

Now I understand what you want to do. You are having trouble with the table filters. is it correct?

flat-fuchsia•2y ago

Yep Well, applying those filters. They display fine

Omer•2y ago

i thought you had problem with auto complete of useSelect 🤦‍♂️

flat-fuchsia•2y ago

lol, no, so sorry for confusion, I should have been more clear

Omer•2y ago

no worries, my bad 👍 Can you share your useTable code?

flat-fuchsia•2y ago

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

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

pretty much exactly this ☝️ (I am using different table names, but I've been sticking with the user/group scenario for this entire thread so I'll continue sticking with it lol) Oh, sorry, useTable

    const { tableProps } = useTable<IUser>({
        metaData: {
            select: 'id, name, groups!inner(id, name)',
        },
    });

    const { tableProps } = useTable<IUser>({
        metaData: {
            select: 'id, name, groups!inner(id, name)',
        },
    });

I don't think !inner(...) is necessary per se, but I've been flipping back and fourth trying to get this to work

Omer•2y ago

I think we can do it with onSearch function of useTable

  const { tableProps } = useTable<IUser>({
         metaData: {
             select: 'id, name, groups!inner(id, name)',
         },
         onSearch: (params) => {
             const filters: CrudFilters = [];
             const { categories } = params;

             filters.push(
                 {
                     field: "groups.id",
                     operator: "in",
                     value: categories,
                 },
             );

             return filters;
         },
     });

  const { tableProps } = useTable<IUser>({
         metaData: {
             select: 'id, name, groups!inner(id, name)',
         },
         onSearch: (params) => {
             const filters: CrudFilters = [];
             const { categories } = params;

             filters.push(
                 {
                     field: "groups.id",
                     operator: "in",
                     value: categories,
                 },
             );

             return filters;
         },
     });

please tell me we're getting closer hehe 😅

flat-fuchsia•2y ago

we've.... got the same result

Omer•2y ago

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

flat-fuchsia•2y ago

aye and no console output if I put a console.log('test'); inside onSearch which suggests it's not being run

Omer•2y ago

Is there any way I can access your project? So I can help faster

flat-fuchsia•2y ago

not atm, I'll spin up a stackblitz and get you something in just a moment It's installing dependencies a moment, but will DM you details now @Omer