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
other-emeraldβ’2y ago
That looks like exactly what I'm looking for, thank you!
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: 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
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:
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 πββοΈ
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.)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
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):
@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.
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