英文:
Count number of items on has_many relationship, including entries with no records
问题
我有两个模型在我的Phoenix应用中：School和User。一个学校可以拥有多个用户。这个关系由SchoolUser模型定义。
我想列出所有学校，并计算每所学校有多少学生。为此，我有以下函数：
def list_schools do
School
|> join(:left, 展开收缩, u in assoc(s, :users))
|> where(展开收缩, u.role == ^:student)
|> group_by(展开收缩, s.id)
|> select(展开收缩, {s, count(s.id)})
|> Repo.all()
end
上述函数用于返回学校列表并计算每所学校有多少学生。然而，它不会返回没有学生的学校。
我该如何为没有学生的学校返回0？
英文:
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, 展开收缩, u in assoc(s, :users))
|> where(展开收缩, u.role == ^:student)
|> group_by(展开收缩, s.id)
|> select(展开收缩, {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?
答案1
得分: 0
我们可以将检查移到 on 子句：
def list_schools do
School
|> join(:left, [c], u in assoc(c, :users), on: u.role == ^:student)
|> group_by(展开收缩, s.id)
|> select(展开收缩, {s, count(s.id)})
|> Repo.all()
end
英文:
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.id)
|> select(展开收缩, {s, count(s.id)})
|> Repo.all()
end
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论