Counting associated model rows with ecto

Question

I'm trying to find how many projects belong to a particular user.

I know that you can do this to get the count of a model:

count = User |> Repo.aggregate(:count, :id)

But how can I find the count of projects that belong to that user?

Answer 1

Here's one way: fetch the user, use assoc/2 to create a query for its associations, and then count it the same way you're doing right now:

User |> Repo.get(123) |> assoc(:projects) |> Repo.aggregate(:count, :id)

Answer 2

Projects table belongs to users table, so it has the foreign key so you can do aggregate count on Project model where project.user_id == ^user_id

import Ecto.Query

def count(user_id: user_id) do
   query = from project in Project,
      where: project.user_id == ^user_id

   Repo.aggregate(query, :sum, :amount)
end