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
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-cyanOPβ’3y ago
That looks like exactly what I'm looking for, thank you!
Hooray! π»
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: 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)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β’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:
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:
@Omer how come we always get in touch when Iβm on vacation. Or maybe I have too many vacations π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-cyanOPβ’3y ago
Thank you @cyborg31 and @Omer - I'll take this and run with it
rising-crimsonβ’3y ago
Iβm fine, always glad to help. Give it to the newcomers. π thanks, much appreciated
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.)Cool. If i am not wrong you can use name path syntax for nested field filters β‘οΈ
setFilters({
field: "groups.id",
value: 1,
operator: "eq"
});
GitHub
refine/index.ts at next Β· pankod/refine
Build your React-based CRUD applications, without constraints. - refine/index.ts at next Β· pankod/refine
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):
@Omer the only output I'm getting is the failed network requests and this:
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.
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 runningIt should have worked. So does your query work directly with supabaseClient? Maybe this can help us
eastern-cyanOPβ’3y ago
It does work directly with supabaseClient, yes
Cool, could you share?
eastern-cyanOPβ’3y ago
so I did a quick test like this:
and got three results spitting out in my console
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
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?I will make an example for you
eastern-cyanOPβ’3y ago
(edit: I misread the first link.)
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
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
ups π We're trying to build this query, right?
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:
Now I understand what you want to do. You are having trouble with the table filters. is it correct?
eastern-cyanOPβ’3y ago
Yep
Well, applying those filters. They display fine
i thought you had problem with auto complete of useSelect π€¦ββοΈ
eastern-cyanOPβ’3y ago
lol, no, so sorry for confusion, I should have been more clear
no worries, my bad π
Can you share your useTable code?
eastern-cyanOPβ’3y ago
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
I don't think
!inner(...)
is necessary per se, but I've been flipping back and fourth trying to get this to workI think we can do it with onSearch function of useTable
please tell me we're getting closer hehe π
eastern-cyanOPβ’3y ago
we've.... got the same result
"{code: '42703', details: null, hint: null, message: 'column users.groups does not exist', statusCode: 42703}" same error?
eastern-cyanOPβ’3y ago
aye
and no console output if I put a
console.log('test');
inside onSearch
which suggests it's not being runIs there any way I can access your project? So I can help faster
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