Rails SQL Queries on join table

Question

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

vee · Accepted Answer · 2016-01-20 06:45:05Z

1

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.

answered Jan 20, 2016 at 6:45

vee

38.7k7 gold badges76 silver badges80 bronze badges

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

1 Comment

dwoodwardgb Over a year ago

I found to_json to be more helpful than to_yaml, but thanks! I think I'm finally starting to understand this stuff.

Collectives™ on Stack Overflow

Rails SQL Queries on join table

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related