Model relationship joins

[karai17]

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

[cycomachead]

This would be huge for me as well. I'm usually just writing custom fetch functions at the moment. :)

[leafo]

@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?

[cycomachead]

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.

[karai17]

@cycomachead Can you show me an example of your fetch function?

[cycomachead]

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

[eko234]

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.