Can I query a model with an aggregate count of one of it's association in Ecto?

Question

Is it possible to compose a query that returns all of a model (i.e. Post) with an aggregate count of one of it's associations (i.e. Like).

Repo.all(from p in Post, 
    join: l in Like, 
    where l.post_id == p.id,
    group_by: p.id,
    select: {p, count(l.id)

I've tried using group_by and select (above), but this only partially works. The results from the query exclude all of posts that don't have any likes.

If this is not possible, what's the best way to handle this problem? First thing that comes to mind mapping over the results of the query like such:

posts = 
    Repo.all Post
    |> Enum.map(fn(post) ->
        likes_query = from l in Like, where: l.post_id == ^post.id
        likes_count = Repo.aggregate(likes_query, :count, :id)

        %{post: post, likes_count: likes_count}
     end)

Answer 1

There are two things wrong with the query:

1. You need to use on instead of where to specify the join condition

2. You need to use left_join instead of join if you want posts with no likes to be returned.

Final query:

from p in Post, 
  left_join: l in Like, 
  on: l.post_id == p.id,
  group_by: p.id,
  select: {p, count(l.id)}

---

You can also use assoc in left_join here which will add the correct on automatically:

from p in Post, 
  left_join: l in assoc(p, :likes), 
  group_by: p.id,
  select: {p, count(l.id)}