Rails 4 joins query display the wrong resource -

Rails 4 joins query display the wrong resource -

i have 2 resources, doctors , reservations.

class doc < activerecord::base has_many :reservations end class reservation < activerecord::base belongs_to :doctor end

schema:

create_table "reservations", force: true |t| t.integer "doctor_id" t.date "date" end create_table "doctors", force: true |t| t.string "description" t.datetime "created_at" t.datetime "updated_at" end

i want display doctors available on date, doctors dont have reservations on day.

my query:

def index @doctors = doctor.all.order("created_at desc") @doctors = @doctors.joins(:reservations).where.not('reservations.date ?',params[:free_on]) if params[:free_on].present? @doctors = @doctors.paginate(:page => params[:page], :per_page => 9) end

my problem is: query gives me reservations result. if have 1 doc , 3 reservations in database, , select 1st of nov (1 reservation on date), shows me 2 times same doctor, 2 reservations not on 1st november.

i tried .group, 1 time again shows me 1 doctors if there reservation...

someone has thought of whats wrong query ?

thank you,

the entire premise of query wrong.

@doctors = @doctors.joins(:reservations).where.not('reservations.date ?',params[:free_on])

you're finding doctors have reservations on days other "free_on" - regardless of whether have reservation on "free_on". that's not want do. want find doctors have no reservations on "free_on".

@doctors = @doctors.where("not exists (select * reservations doctor_id=doctors.id , reservations.date=?)", params[:free_on])

that should optimizable utilize indices , run pretty fast.

ruby-on-rails rails-activerecord