我能够运行此查询来生成区域哈希并按答案分组(包括零计数):
Region.includes(:answers).group("regions.region").count("answers")
生产:
{"blar"=>0, "East"=>0, "East Midlands"=>11, "London"=>8, "North East"=>0, "North West"=>0, "Northern Ireland"=>0, "Rest of World"=>89, "Scotland"=>0, "South East"=>0, "South West"=>0, "Wales"=>0, "West Midlands"=>0, "Yorkshire and the Humber"=>0}
但是,当我想要特定问题的此结果(包括计数零)时,它仅显示带有答案的区域哈希。
查询:
Region.includes(:answers).where("answers.question_id = 14").group("regions.region").count("answers")
生产:
{"East Midlands"=>3, "London"=>1, "Rest of World"=>4}
我知道查询将只选择question_id的答案,所以这给出了给定的输出,但我尝试了许多不同的查询(包括 LEFT OUT JOINS)并且无法获得所需的结果。
供参考:
地区has_many:答案
回答belongs_to:地区
回答belongs_to:问题
谢谢
对于任何感兴趣的人,我能够通过将 sql 是 null 或 WHERE 子句添加到 where 子句来解决这个问题,如下所示:
Region.includes(:answers).where("answers.id IS NULL OR answers.question_id = 14").group("regions.region").count("answers")
生产:
{"blar"=>0, "East"=>0, "East Midlands"=>1, "London"=>0, "North East"=>0, "North West"=>0, "Northern Ireland"=>0, "Rest of World"=>4, "Scotland"=>0, "South East"=>0, "South West"=>0, "Wales"=>0, "West Midlands"=>0, "Yorkshire and the Humber"=>0}
可能不是最好的方法,但它完成了工作
添加显式 NULL 的 SELECT,如下所示:
Region.outer_joins(:answers).select('region.name IS NULL').