Count number of items on has_many relationship, including entries with no records

Question

I have two models in my Phoenix app: School and User. A school can have many users. This relationship is defined by the SchoolUser model.

I want to list all schools and count how many students each school has. For that, I have the following function:

def list_schools do
  School
  |> join(:left, [s], u in assoc(s, :users))
  |> where([s, u], u.role == ^:student)
  |> group_by([s], s.id)
  |> select([s], {s, count(s.id)})
  |> Repo.all()
end

The function above works for returning a list of schools and counting how many students each school has. However, it doesn't return schools that don't have any students.

How can I return 0 for schools with no students?

Answer 1

We can move the check to the on clause:

def list_schools do
  School
  |> join(:left, [c], u in assoc(c, :users), on: u.role == ^:student)
  |> group_by([s], s.id)
  |> select([s], {s, count(s.id)})
  |> Repo.all()
end