CODEWITHSUNDEEP
ruby-on-railsactiverecord
hokenpoke
hokenpoke

Reputation: 23

How to make query conditional on associated table in ActiveRecord

I am trying to create a query that will return distinct records from one table based on a conditions in an associated table.

Specifically, there is an Act model which has_many Events and, of course 'Event' belongs_to Act I would like to find those Acts that do not have an Event on a particular date.

The query needs to be run with a not condition because otherwise the results will not exclude any Act records.

I have tried a variety of constructions including using merge across class methods but it has not worked.

#Act model
def self.busy_on(date)
    joins(:event).merge.not(Event.busy_on(date))
end

#Event model
def self.busy_on(date)
    where(date: date)
end

#Controller
Act.busy_on(params[:date])

The above is giving me an argument error (expect 1 got 0) but I'm not sure that it's right in the first place.

Upvotes: 1

Views: 1228

Answers (4)

engineersmnky
engineersmnky

Reputation: 29478

Here is how I would recommend going about this.

We will create inverted scopes for busy and available like so 

class Act < ApplicationRecord
   has_many :events 

   scope :busy_on, ->(date) { joins(:events).where(events: {date: date}) }
   scope :available_on, ->(date) {where.not(id: busy_on(date).select(:id))}
end

Here we create one scope for the days that an Act is busy and then we use that scope as a counter filter to determine if the act is available. The resulting SQL for busy_on scope will be: 

  SELECT 
     acts.* 
  FROM 
     acts
     INNER JOIN events ON acts.id = events.act_id
  WHERE 
     events.date = [THE DATE YOU PASS INTO THE SCOPE]

Thus the resulting SQL for the available_on scope will be:

 SELECT 
   acts.* 
 FROM 
   acts 
 WHERE 
   acts.id NOT IN ( 
      SELECT 
         acts.id
      FROM 
         acts
         INNER JOIN events ON acts.id = events.act_id
      WHERE 
         events.date = [THE DATE YOU PASS INTO THE SCOPE]
   )

Upvotes: 0

Anand
Anand

Reputation: 6531

class Act < ApplicationRecord
  def self.busy_on(date)
    #includes(:events).where(events: {created_at: date, id: nil })
   includes(:events).where("events.created_at = ? AND events.id = ?", date, nil)
  end
end

Upvotes: 0

hokenpoke
hokenpoke

Reputation: 23

Not sure this is the most elegant or 'correct' way to do it but it's the only thing I've been able to make work:

#Event model
def self.act_busy_on(date)
    where(date: date).pluck(:act_id)
  end

#Act model
def self.available_on(date)
    where.not(id: Event.act_busy_on(date))
  end

#Controller
Act.available_on(params[:date])

Upvotes: 1

Vishal
Vishal

Reputation: 828

First of all, you need to modify event to events in the joins query.

You asked for Acts which are free on a particular date in your question so the query would be like,

def get_acts(date)
    includes(:events).where('events.date = ? and events.id = ?', date, nil)
end

Now if you call Act.get_acts(26-05-2019), will give you acts which are free on that particular day.

Upvotes: 0

Related Questions