CODEWITHSUNDEEP
mysqlsqlruby-on-rails
Mark Locklear
Mark Locklear

Reputation: 5325

Mysql join in rails

I have two tables orientations and registrations that I would like to perform a join query on. Here is the schema for each of the tables...

create_table "orientations", :force => true do |t|
    t.date     "class_date"
    t.text     "class_time"
    t.integer  "seats"
    t.boolean  "active",     :default => true
    t.datetime "created_at",                   :null => false
    t.datetime "updated_at",                   :null => false
  end

 create_table "registrations", :force => true do |t|
    t.integer  "orientation_id"
    t.string   "first_name"
    t.string   "last_name"
    t.string   "email"
    t.string   "student_id"
    t.string   "phone"
    t.string   "registration_cancellation_token"
    t.datetime "registration_cancelled_at"
    t.boolean  "checked_in",                      :default => false
    t.boolean  "cancelled",                       :default => false
    t.datetime "created_at",                                         :null => false
    t.datetime "updated_at",                                         :null => false
    t.text     "program"
  end

What I am looking for is all registrations for all orientations between two dates. I came up with this...

Registration.where(Orientation.where created_at: => @start_date..@end_date)

Of course that syntax is bogus, but hopfully it will help get across what I am looking for.

Upvotes: 0

Views: 47

Answers (1)

Kirti Thorat
Kirti Thorat

Reputation: 53018

Use this to get all registrations for all orientations between two dates:

Registration.joins(:orientation).where(orientations: {:class_date => @start_date..@end_date})

joins performs an INNER JOIN which filters the rows that don't have association.

But, if you wish to keep the rows that don't have associations then go for includes which performs a LEFT OUTER JOIN 

Registration.includes(:orientation).where(orientations: {:class_date => @start_date..@end_date})

Upvotes: 1

Related Questions