CODEWITHSUNDEEP
ruby-on-railsrubyactiverecord
jayqui
jayqui

Reputation: 1899

ActiveRecord: Group by count of has_many: through model

Given these classes

class Account < ApplicationRecord
  has_many :team_memberships
  has_many :follow_ups, through: :team_memberships
end

class TeamMembership < ApplicationRecord
  belongs_to :account
  has_many :follow_ups
end

class FollowUp < ApplicationRecord
  belongs_to :team_membership
  has_one :account, through: :team_membership
end

I'm looking for the idiomatic way to query in ActiveRecord to get all Accounts that have more than one incomplete FollowUp (i.e. Accounts that have more than one FollowUp with a completed_at of nil).

A slow way, which uses Ruby but does not compile entirely to SQL, would be

Account.all.select do |acc| 
  acc.follow_ups.where(completed_at: nil).count > 1 
end

But what's the proper ActiveRecord query for this?

Upvotes: 0

Views: 82

Answers (1)

Igor Drozdov
Igor Drozdov

Reputation: 15055

query in ActiveRecord to get all Accounts that have more than one incomplete FollowUp

It can be solved just by join the follow_ups to accounts and filtering by the follow_ups.completed_at:

Account.joins(:follow_ups).where(follow_ups: { completed_at: nil })

If you need the number of incomplete follow_ups to be more than a particular number, then GROUP BY and HAVING may help:

Account
  .joins(:follow_ups)
  .where(follow_ups: { completed_at: nil })
  .group(:id)
  .having("COUNT(follow_ups.id) > 1")

Upvotes: 2

Related Questions