Rails query with group and count must include zeros

Question

I know this question has been asked before but I have read many of the posts and tried applying them to my situation and I just can't get it working. I'm a beginner who could use some help. Here are my models:

class Action < ActiveRecord::Base
  belongs_to :Student
  belongs_to :Meeting
  belongs_to :ClassSection
end

class Meeting < ActiveRecord::Base
  belongs_to :floorplan
  has_many :actions
  belongs_to :class_section

end

I am trying to get a count of actions for a single student across multiple meetings, including meetings where he/she had 0 actions. So, if there are two rows in the meetings table, ID=1 and ID=2, and student 83 has a single action with meeting_id=1 in the actions table, my query, which will include where(:student_id=>83) somewhere, should return something like

1=>1

2=>0

I hope this makes sense. I know the answer involves outer joins but I am bad at implementing them in pure SQL and worse at doing them through ActiveRecord. FYI I am using MYSQL. Thanks in advance for whatever help you can provide.

unrelated to your question, but typical style would have the belongs_to associations all downcase: belongs_to :student. — Xavier Shay
– Xavier Shay, Commented Jan 20, 2015 at 2:57

Xavier Shay · Accepted Answer · 2015-01-20 02:52:07Z

Meeting
  .joins('LEFT JOIN actions ON meeting_id = meetings.id')
  .where(student_id: 83)
  .group('meetings.id')
  .count('actions.id')

Explanation

.joins is the left/outer join that you intuited that you needed. It means "include at least one row for every meeting, even if there are no actions".
.group needs to be on the meeting id, since this will always be present and different meetings should be grouped separately.
.count needs to be on actions id. COUNT does not count null records, so meetings with no actions will be counted as 0.

It's a little bit weird that for a count of actions you nee to start your query with Meeting, but that is necessary when you want to include 0 counts. Otherwise there would be no way for SQL to know what meetings were missing!

For reference, the generated SQL is:

SELECT
  COUNT(actions.id) AS count_actions_id,
  meetings.id AS meetings_id
FROM "meetings" LEFT JOIN actions ON meeting_id = meetings.id
GROUP BY meetings.id

Mohammad AbuShady · Accepted Answer · 2015-01-20 02:47:00Z

0

I think this should work fine just by grouping

Meeting.where(student_id: 83).group(:actions).count

This will return the hash you want

{1=>1, 2=>0}

answered Jan 20, 2015 at 2:47

Mohammad AbuShady

43.4k11 gold badges81 silver badges89 bronze badges

2 Comments

Xavier Shay Over a year ago

what version of ARel? This doesn't work for me - the generated SQL is invalid.

Mohammad AbuShady Over a year ago

what error did you get?, could you paste me the query with to_sql

Collectives™ on Stack Overflow

Rails query with group and count must include zeros

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related