lapis
lapis copied to clipboard
Model relationship joins
As discussed on Discord yesterday, I would like to see the ability to model joins via relations at some point. I am posting this issue so I can keep track of it in my code~
My main use case at the moment would be to select a list of events
from the database through a player
object using the registrations
table as a filter.
local id = 2
local player = Players:get(id)
local events = player:get_events()
SELECT * FROM events LEFT JOIN registrations ON registrations.player_id=2;
Players
id | name |
---|---|
1 | Alice |
2 | Bob |
Events
id | name |
---|---|
1 | Some Event |
2 | Another Event |
Registrations
id | event_id | player_id |
---|---|---|
1 | 1 | 1 |
2 | 1 | 2 |
3 | 2 | 2 |
This would be huge for me as well. I'm usually just writing custom fetch
functions at the moment. :)
@cycomachead if possible, the preferred approach to load data is to use preload
Does preload
not work for you because you need to filter across a relations? or is there another use case?
To be honest, it might, but I couldn't quite get it right the first couple tries.
In the main case, I have a "has-many-through" relationship, much like the OP. There's a "collection" which contains a bunch of projects through a "collection_memberships" join table.
in this case I want to be able to write collection:get_projects()
which I have by just writing a fetch
with a JOIN
on collection_memberships.
@cycomachead Can you show me an example of your fetch function?
Sure, in this case I ended up using a subquery, but could have written a join. I didn't test which would be ore performant... https://github.com/bromagosa/snapCloud/blob/master/models.lua#L77
I just found a way to solve this kind of situations, indeed lapis model system is very nice in this regard, i do not use lua or moonscript but the following code in fennel lets me retrieve a user, its posts, and its colors, which are a many to many relationship with a table in the middle user_colors:
(app:get "/users"
(fn []
(let [user (Users:find "1")
posts (user:get_posts)
colors (user:get_user_colors)]
(each [i v (ipairs colors)]
(v:get_color))
{:json {: user }})))
I find it easier and more powerful than django rest framework serializers, very awesome leafo.