CODEWITHSUNDEEP
ruby-on-railsruby-on-rails-3
MikeC
MikeC

Reputation: 480

Rails query with group and count must include zeros

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.

Upvotes: 2

Views: 1655

Answers (2)

Xavier Shay
Xavier Shay

Reputation: 4127

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

Upvotes: 3

Mohammad AbuShady
Mohammad AbuShady

Reputation: 42849

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}

Upvotes: 0

Related Questions