Rails: group and count sql query include zero's

Question

I'm able to run this query to generate a hash of Regions and group by answer (including zero count):

Region.includes(:answers).group("regions.region").count("answers")

produces:

{"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}

However when I want this result (to include count zeros) for a particular question, it only displays a hash of the regions with an answer.

Query:

Region.includes(:answers).where("answers.question_id = 14").group("regions.region").count("answers")

produces:

{"East Midlands"=>3, "London"=>1, "Rest of World"=>4}

I understand the query will be selecting only answers for question_id, so this gives the given output but I've tried many different queries (including LEFT OUT JOINS) and been unable to get the desired result.

For reference:

Region has_many :answers

Answer belongs_to :region

Answer belongs_to :question

Thanks

Ben G · Accepted Answer · 2013-10-16 11:04:00Z

1

For anyone interested, I was able to solve this by adding the sql IS NULL OR to the where clause as so:

Region.includes(:answers).where("answers.id IS NULL OR answers.question_id = 14").group("regions.region").count("answers")

Producing:

{"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}

Probably not the best way but it did the job

answered Oct 16, 2013 at 11:04

Ben G

1282 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

nikolayp · Accepted Answer · 2013-10-15 15:31:38Z

0

Add SELECT with NULLs explicit, like following:

Region.outer_joins(:answers).select('region.name IS NULL').

answered Oct 15, 2013 at 15:31

nikolayp

18.1k4 gold badges69 silver badges68 bronze badges

1 Comment

Ben G Over a year ago

The result is the same if I include this select method

Collectives™ on Stack Overflow

Rails: group and count sql query include zero's

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related