CODEWITHSUNDEEP
ruby-on-railsrubypostgresqlactiverecord
user1032369
user1032369

Reputation: 589

Rails ActiveRecord: Query a many-to-many association with a where on the join table

So I have three models, User, Course, and Registration. There is a many to many association between User and Course through Registration.

class Registration < ActiveRecord::Base
  belongs_to :user
  belongs_to :course
end

class Course < ActiveRecord::Base

  has_many :registrations
  has_many :users, through: :registrations
end

class User < ActiveRecord::Base

  has_many :registrations
  has_many :courses, through: :registrations
end

Real simple stuff, and everything works how it is supposed to, but there is also a field in registrations which I would like to base my queries off of. There is a "role" field in registrations, and I would like to sometimes select all people in a certain course with a certain role. Currently I do that in two queries, is there a way to do this in a single query?

Here is what I currently do:

course = Course.find(1)
registration = course.registrations.where(role: "some_role").limit(1)[0]
user = registration.user

As you can see this would be three queries, is there a way to combine the last two call so that there is only one SQL query for those two in a simple way?

Upvotes: 2

Views: 1786

Answers (2)

user1032369
user1032369

Reputation: 589

I have solved my own problem. Here is how:

@course = Course.find_by_access_code(params[:access_code])
@instructor = @course.users.where("registrations.role = ?", 'instructor').limit(1)[0]

This does what I wanted it to do, shorten the 3 SQL queries into 2.

Thanks anyone who tried to help!

Upvotes: 0

B Seven
B Seven

Reputation: 45943

user = Registration.includes(:user).find_by(role: 'some_role', course:course).user

The problem with this query is that if there is no such registration, it will raise an exception. So, either you need to handle the exception, or guarantee that the registration exists.

I don't think handling the exception is a very clean approach. An extra DB call may not affect performance, but will make the code easier to read and test.

To reduce the number of queries, you will need to eager load the user. See Rails Guide. This is similar to the N+1 problem.

Alternately, you may want to do

course = Course.find(1)
registration = course.registrations.find_by(role: "some_role")
user = registration.user if registration

If you don't need the course, you can do:

registration = Registration.find_by(course_id: 1, role: "some_role")
user = registration.user if registration

Upvotes: 2

Related Questions