Mysql join in rails

Question

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.

Registration.includes([:orientation]).where("orientation.created_at between ? and ?", @start_date, @end_date) ...try with this and let me know — userRandom
– userRandom, Commented Jun 13, 2014 at 15:54

Kirti Thorat · Accepted Answer · 2014-06-16 21:19:02Z

1

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})

edited Jun 16, 2014 at 21:19

answered Jun 13, 2014 at 16:06

Kirti Thorat♦

53.1k9 gold badges105 silver badges108 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Mark Locklear Over a year ago

Thanks Kirti, but these all return no results. FYI some of the registrations have nil orientation_id's. Would this cause the query to return no results?

Mark Locklear Over a year ago

Interesting. If I use the 2nd query (includes) and I use a puts to view the results in the console I get an empty array returned. Here is the console output: pastie.org/9287293. However if I go to my mysql DB and execute 'SELECT orientations.* FROM orientations WHERE (orientations.class_date BETWEEN '2014-06-01' AND '2014-06-07');' then it returns valid results.

Kirti Thorat Over a year ago

@Lumbee Registrations with nil orientation_id's will not be part of results in the first query as it performs INNER JOIN. But second query should cover those due to LEFT OUTER JOIN. Also, you want the condition on class_date column or created_at column? As in the question you mentioned created_at and in the above comment you are using class_date? in mysql

Mark Locklear Over a year ago

Hey @Kirti, you are correct! I needed to search based on :class_date. Once I did that, the queries worked. You rock! Thanx!

Kirti Thorat Over a year ago

Glad to help. :) I will update my answer to reflect :class_date attribute.

Collectives™ on Stack Overflow

Mysql join in rails

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related