eastern-cyan
eastern-cyanβ€’3y 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
Omerβ€’3y 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
eastern-cyan
eastern-cyanOPβ€’3y ago
That looks like exactly what I'm looking for, thank you!
Omer
Omerβ€’3y ago
Hooray! 🍻
eastern-cyan
eastern-cyanOPβ€’3y 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
Omerβ€’3y 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
rising-crimson
rising-crimsonβ€’3y 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
Omerβ€’3y 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 πŸ„β€β™‚οΈ
eastern-cyan
eastern-cyanOPβ€’3y ago
Thank you @cyborg31 and @Omer - I'll take this and run with it
rising-crimson
rising-crimsonβ€’3y ago
Iβ€˜m fine, always glad to help. Give it to the newcomers. πŸ™‚ thanks, much appreciated
eastern-cyan
eastern-cyanOPβ€’3y 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
Omerβ€’3y 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
Omerβ€’3y ago
GitHub
refine/index.ts at next Β· pankod/refine
Build your React-based CRUD applications, without constraints. - refine/index.ts at next Β· pankod/refine
eastern-cyan
eastern-cyanOPβ€’3y 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
Omerβ€’3y ago
eastern-cyan
eastern-cyanOPβ€’3y 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
Omerβ€’3y ago
It should have worked. So does your query work directly with supabaseClient? Maybe this can help us
eastern-cyan
eastern-cyanOPβ€’3y ago
It does work directly with supabaseClient, yes
Omer
Omerβ€’3y ago
Cool, could you share?
eastern-cyan
eastern-cyanOPβ€’3y 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
Omerβ€’3y ago
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
eastern-cyan
eastern-cyanOPβ€’3y 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
Omerβ€’3y ago
I will make an example for you
eastern-cyan
eastern-cyanOPβ€’3y ago
(edit: I misread the first link.)
Omer
Omerβ€’3y 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(*)',
},
});
No description
eastern-cyan
eastern-cyanOPβ€’3y 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
Omerβ€’3y 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']);
eastern-cyan
eastern-cyanOPβ€’3y 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
Omerβ€’3y ago
Now I understand what you want to do. You are having trouble with the table filters. is it correct?
eastern-cyan
eastern-cyanOPβ€’3y ago
Yep Well, applying those filters. They display fine
Omer
Omerβ€’3y ago
i thought you had problem with auto complete of useSelect πŸ€¦β€β™‚οΈ
eastern-cyan
eastern-cyanOPβ€’3y ago
lol, no, so sorry for confusion, I should have been more clear
Omer
Omerβ€’3y ago
no worries, my bad πŸ‘ Can you share your useTable code?
eastern-cyan
eastern-cyanOPβ€’3y 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
Omerβ€’3y 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 πŸ˜…
eastern-cyan
eastern-cyanOPβ€’3y ago
we've.... got the same result
Omer
Omerβ€’3y ago
"{code: '42703', details: null, hint: null, message: 'column users.groups does not exist', statusCode: 42703}" same error?
eastern-cyan
eastern-cyanOPβ€’3y ago
aye and no console output if I put a console.log('test'); inside onSearch which suggests it's not being run
Omer
Omerβ€’3y ago
Is there any way I can access your project? So I can help faster
eastern-cyan
eastern-cyanOPβ€’3y 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