CODEWITHSUNDEEP
ruby-on-railsrubyactiverecord
Mateusz Urbański
Mateusz Urbański

Reputation: 7862

Has many association objects count

In my application I have model Car which has_many :reservations

class Car < ActiveRecord::Base
 has_many :reservations
end

class Reservation < Activerecord::Base
  belongs_to :car
end

Now i want to pull out from database all cars that have no reservations. How Can I do that?

Edit: Thank's everyone for help but now I have other problem: My search method search Cars that not have reservations or not have reservations i proper dates. It looks like this:

def self.is_not_reservated(handover_date, return_date)
  without_reservation = where("cars.id NOT IN (?)", Reservation.pluck(:car_id)) 
  with_reservation = joins(:reservations).where.not("reservations.reception_time <= ? 
    AND reservations.return_time >= ?", return_date, handover_date) 
end

Now when I try to add this two ActiveRecordRelation object I will have an array but I still want to have an ActiveRecordRelation object... Is there any way to solve this problem. Thank's in advance!

My solution: I don't know that this a good way to do such a thing but it works as expected :)

def is_not_reservated(handover_date, return_date)
      without_reservation = where("cars.id NOT IN (?)", Reservation.pluck(:car_id)) 
      with_reservation = joins(:reservations).where.not("reservations.reception_time <= ? 
        AND reservations.return_time >= ?", return_date, handover_date) 
      cars = without_reservation + with_reservation
      where(id: cars.map(&:id))
    end

Upvotes: 1

Views: 604

Answers (6)

Surya
Surya

Reputation: 16012

Either you can create a method where you run a plain SQL query inside apps/models/car.rb:

def unreserved_cars
  sql = "Select * from #{self.table_name} where(#{Reservation.table_name}.car_id != id)"
  records_array = ActiveRecord::Base.connection.execute(sql)
end

Or if you need ActiveRecord::Relation objects then you can create a scope inside apps/models/car.rb:

scope :unreserved_cars, -> { where("#{Reservation.table_name}.car_id != id") }

Then you can do something like:

Car.unreserved_cars

Upvotes: 0

Richard Peck
Richard Peck

Reputation: 76784

To further RSB's answer, you may also want to use a scope (basically a class method):

#app/models/car.rb
Class Car < ActiveRecord::Base
   scope :unreserved, -> { where("cars.id NOT IN (?)", Reservation.pluck(:car_id)) }
end

This will allow you to call Car.unreserved

Upvotes: 2

rejin
rejin

Reputation: 179

something like this?

Car.joins(:reservations).group("cars.id HAVING count(reservations.id) > 0")
Car.joins(:reservations).group("cars.id").having("count(reservations.id) > ?",0)

Upvotes: 1

Patrick Oscity
Patrick Oscity

Reputation: 54694

You can also do it all in the database, even without having to use strings in the query:

Car.includes(:reservations).where(reservations: {car_id: nil})

# SELECT * FROM "cars"
# LEFT OUTER JOIN "reservations" ON "reservations"."car_id" = "cars"."id"
# WHERE "reservations"."car_id" IS NULL

Upvotes: 3

Rajdeep Singh
Rajdeep Singh

Reputation: 17834

Are you looking for this?

Car.where("cars.id NOT IN (?)", Reservation.pluck(:car_id))

Upvotes: 2

Mohanraj
Mohanraj

Reputation: 4210

Exactly you need to use the left outer join. It should be something like below,

Car.joins('LEFT OUTER JOIN reservations ON reservations.car_id = car.id')

For more information please refer this rails documentation http://guides.rubyonrails.org/active_record_querying.html#using-a-string-sql-fragment

Upvotes: 3

Related Questions