sql - rails : group and count sql query include zero's

Question

我能够运行此查询以生成区域哈希并按答案分组(包括零计数):

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)，但无法获得所需的结果。

供引用:

区域有_many :answers

回答 belongs_to :region

回答 belongs_to :question

谢谢

Answer 1

对于任何感兴趣的人，我能够通过将 sql IS NULL OR 添加到 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} 

可能不是最好的方法，但它完成了工作