Can I clean up this ecto query that uses a lateral join?

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?

If I need to create a lateral join, I might also be modeling something that needs to be named. In that case, creating a database view to encapsulate that hidden model, and a new Ecto schema might clean it up. At that point, your query might be a simpler join or where statement.

2 Likes