Ecto query, take 1 item from subquery

Questions / Help
1

I’ve looked at [this thread[(Proper way to subquery on a join in ecto) which was a little bit helpful, but still stuck trying to write what I’d consider a simple SQL query:

Let’s take a known domain, Posts and Comments (but multi-tenant, so multiple accounts on the system that have Posts).

I’d like to have the last comment of all Posts for instance, here’s what my naive first try was:

def x(account_id) do
  last_comment =
    from c in Comment,
    where: c.account_id == ^account_id,
    limit: 1,
    order_by: [desc: :inserted_at]

  query =
    from p in Post,
    join: c in subquery(last_comment), on: c.post_id == p.id,
    where: p.account_id == ^account_id,
    select: %{post: p, last_comment: c}
end

So you might have got that the limit: 1 in the subquery is the issue.

I’m not able to figure out how I can let the subquery have all its results, yet need to take the last one.

Here’s what I’d doin SQL:

SELECT p.*, (SELECT c.body FROM comments WHERE c.account_id = ? AND c.post_id = p.id)
FROM posts p
WHERE p.account_id = ?

Now ideally I’d like to have more than one field in Elixir, so I recon it’s not exactly a translation of that query above, but it’s just to make things clear.

Any help / pointers would be highly appreciated,
Thanks

1 Like
2

There’s an example in the docs for a lateral join that does pretty much what you want: Ecto.Query — Ecto v3.6.1

Only difference being that it takes the top 5 Comments for a Post, whereas you want the last Comment for a Post.

2 Likes
3

thanks exactly what I needed.

1 Like
4

PostgreSQL has a dialect syntax for this:

SELECT DISTINCT ON (post_id) *
FROM comments
WHERE account_id = ?
ORDER BY post_id, inserted_at DESC

I think maybe there’s a way of using fragment to achieve your goal in Ecto.