CODEWITHSUNDEEP
mysqlruby-on-railsactiverecord
dwoodwardgb
dwoodwardgb

Reputation: 174

Rails SQL Queries on join table

I've got a rails app with the following models:

class Partner < ActiveRecord::Base
  has_many :timesheets
  has_many :timeslots, through: :timesheets
  # ...
end

class Timeslot < ActiveRecord::Base
  has_many :timesheets
  has_many :partners, through: :timesheets
  # ...
end

class Timesheet < ActiveRecord::Base
  belongs_to :partner
  belongs_to :timeslot
  # ...
end

Notice how the Timesheet serves as a join table of sorts for storing Partner-Timeslot pairs. I want to get all such Partner-Timeslot pairs where the timeslots fulfill some condition. I don't actually care at all about the Timesheet object for this query. I've worked out that the appropriate SQL is something along the lines of:

SELECT partners.*, timeslots.*
FROM timesheets
  JOIN partners
    ON timesheets.partner_id = partners.id
  JOIN timeslots
    ON timesheets.timeslot_id = timeslots.id
WHERE (...some condition logic that I can figure out on my own...)

Notice how although I'm querying the timesheets table I actually only want Partner and Timeslot objects.

Can you do this with Rails?

Is there a better way to do it?

Miscellaneous info:

I'm using MySQL as the database

Upvotes: 1

Views: 136

Answers (1)

vee
vee

Reputation: 38645

Yes you can certainly do this in Rails. You could start with: 

partner_timeslots = Partner.joins([ timesheets: :timeslot ]).select('partners.*, timeslots.*')

This will generate the following query: 

select partners.*, timeslots.* 
from partners 
  inner join timesheets on timesheets.partner_id = partners.id 
  inner join timeslots on timeslots.id = timesheets.timeslot_id

If trying on console, try partner_timeslots.to_yaml to see all partners and timeslots attributes returned.

Upvotes: 1

Related Questions