Here is the most import part of the query that I am working on
Post
|> where([posts], posts.id in ^post_ids)
|> join(:inner_lateral, [posts], messages in fragment("SELECT * FROM (SELECT DISTINCT ON (user_id) user_id, inserted_at FROM messages WHERE room_id = ? ORDER BY messages.user_id) distinct_messages ORDER BY distinct_messages.inserted_at DESC LIMIT 2", posts.room_id))
|> Repo.all()
For readability (without scrolling) here is just the fragment string itself:
SELECT * FROM (SELECT DISTINCT ON (user_id) user_id, inserted_at FROM messages WHERE room_id = ? ORDER BY messages.user_id) distinct_messages ORDER BY distinct_messages.inserted_at DESC LIMIT 2
Posts has a room_id
and messages has a room_id
as well, so that is what is used for the lateral join. However since Ecto doesn’t support lateral joins natively yet I need to use a fragment. However, I would like to limit the portion of the query that is actually in the fragment as much as possible (so that I can make use of the static ecto schemas that have been defined), but I am not sure how to do that.
I have thought about using dynamic/2
and subquery/2
but they do not seem to fix my problem because they cannot be used with the lateral join (as far as I can tell). Is there something that I am missing?