如何创建一个范围来查找“帖子为零的作者”？
使用 Rails 3，此范围按预期工作：
scope :with_posts, lambda {
joins(:posts).
select("authors.*, count(posts.id) posts_count").
group("posts.author_id").
having("posts_count > 0")
}
生成的 SQL 是：
SELECT authors.*, count(posts.id) posts_count FROM `authors` INNER JOIN `posts` ON `posts`.`author_id` = `author`.`id` GROUP BY posts.author_id HAVING posts_count > 0
但其逆操作不返回任何结果：
scope :with_posts, lambda {
joins(:posts).
select("authors.*, count(posts.id) posts_count").
group("posts.author_id").
having("posts_count < 1")
}
我假设第三行根本没有选择帖子数为零的作者...那么解决方案是什么？
Using Rails 3, this scope works as would be expected:
scope :with_posts, lambda {
joins(:posts).
select("authors.*, count(posts.id) posts_count").
group("posts.author_id").
having("posts_count > 0")
}
The generated SQL is:
SELECT authors.*, count(posts.id) posts_count FROM `authors` INNER JOIN `posts` ON `posts`.`author_id` = `author`.`id` GROUP BY posts.author_id HAVING posts_count > 0
But its inverse returns no results:
scope :with_posts, lambda {
joins(:posts).
select("authors.*, count(posts.id) posts_count").
group("posts.author_id").
having("posts_count < 1")
}
I'm assuming that authors with zero posts are simply not being selected by line three... so what is the solution?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
你是对的，连接排除了所有帖子为零的作者，你需要的是一个外部连接。
我不知道 Rails 3 语法，但在 Rails 2 中，您可以使用 SQL 片段指定连接语句，而不是简单地使用关系名称。
User.all(:joins => 'users.id = posts.user_id 上的外部连接帖子')Rails 3 必须有一个等效的符号，但我从未使用过它，所以我不知道确切的语法。但这应该是总体想法。
You are correct, the join is excluding all authors with zero posts, what you need is an outer join.
I don't know rails 3 syntax, but in rails 2, you can specify the joins statement with an SQL fragment instead of simply using the relation name.
User.all(:joins => 'outer join posts on users.id = posts.user_id')Rails 3 must have an equivalent notation, but I never used it so I don't know the exact syntax. This should be the general idea though.
由于正在创建内连接 SQL，因此它将显示结果以及两个表中可用的记录。因此，尝试创建外连接并在下一个表中查找/计算空值
尝试外连接
Since inner joing SQL is being created it will show up the results with records available in both tables. So try creating outer join and find/count the null values in next table
try outer join
如果您的数据库中有author_id 索引，则此方法效果最佳。
This will work best if you have an index on author_id in your DB.