Rails 6 How to find element where association count is less than 2-CodePudding

I have a problem with extracting data with one query. My two models looks like that:

# school.rb
class School < ActiveRecord::Base
  has_many :meetings
end

# meetings.rb

class Meeting < ActiveRecord::Base
  belongs_to :school
  belongs_to :user
  
  # fields
  # status: [accepted, not_accepted, finished]
en

I want to take every school where passed User has less than 2 meetings with status finished. I was trying something like this:

School.joins(:meetings).where(meetings: { user: User, status: 'finished' }).group(:id).having( 'count(meetings.id) < 2')

But it works if User has one finished meeting in each school. I wonder if it is possible to solve this with one query? Maybe some of you knows if it is possible to do this and how?

@Edit An example for easier understanding of what I would like to receive:

  School | User | Meeting
 1.  A   | Adam | finished 
 2.  A   | Adam | accepted
 3.  A   | Adam | finished
 4.  B   | Adam | accepted
 5.  C   | John | finished
 6.  D   |  -   |    -
 7.  E   | John | finished

So I want to create query which will returns school B, C, D and E for user Adam

CodePudding user response：

I think you need to reverse your thinking here: get the schools you want to EXCLUDE from the results instead of trying to add extra ones to your query. Something like this:

# Get schools with more than 1 meeting for the user
excluded_schools = School.joins(:meetings)
  .where(meetings: { user: User, status: 'finished' })
  .group(:id)
  .having('count(meetings.id) > 1')

# Get ALL schools except for the ones you didn't want
filtered_schools = School.where.not(id: excluded_schools.select(:id))

Using select(:id) instead of pluck(:id) avoids triggering a new database query, so all of this should take just 1 database query when used inside a method. Of course in the console you'll have to nest these instead of storing in variables if you want to accomplish everything in 1 query.

CodePudding user response：

School.left_joins(:meetings).where(meetings: { user: user })
  .or(School.left_joins(:meetings).where(meetings: { id: nil }))
  .group(:id)
  .having("(count(meetings.id) < 2 AND meetings.status = 'finished') OR meetings.id IS NULL")