CODEWITHSUNDEEP
ruby-on-railsactiverecord
Ian Dickinson
Ian Dickinson

Reputation: 13305

Multiple-level join condition in ActiveRecord

I'm struggling to set a condition on a multiple-level join in my Rails ActiveRecord query:

class CourseSlot < ApplicationRecord
  belongs_to :course
  has_many :user, through: :course
end

class Course < ApplicationRecord
  has_and_belongs_to_many :users
end

class User < ApplicationRecord
  has_and_belongs_to_many :courses
end

The HABTM association is stored in a CoursesUsers table:

create_table "courses_users", id: false, force: :cascade do |t|
  t.bigint "user_id", null: false
  t.bigint "course_id", null: false
end

I want to query for any slot whose users include the current user. I tried:

current = User.find(current_user_id)
CourseSlot
  .joins(course: :users)
  .where(course: {users: current})

But this gives me an error:

SELECT  "course_slots".* 
FROM "course_slots" 
INNER JOIN "courses" ON "courses"."id" = "course_slots"."course_id"
INNER JOIN "courses_users" ON 
  "courses_users"."course_id" = "courses"."id" 
INNER JOIN "users" ON "users"."id" = "courses_users"."user_id" 
WHERE "course"."user_id" = 2 
ORDER BY "course_slots"."id" ASC LIMIT $1  [["LIMIT", 1]]
*** ActiveRecord::StatementInvalid Exception: PG::UndefinedTable: 
ERROR:  missing FROM-clause entry for table "course"

I can see that the query is missing a FROM clause, but I've struggled in vain to find a way to get ActiveRecord to generate the right query!

Upvotes: 3

Views: 1511

Answers (1)

Ganesh
Ganesh

Reputation: 2004

Try code like below

CourseSlot.joins(course: :users).where(courses: { users: { id: current_user_id } })

In your query table name is used as 'course' but i think it should be courses

WHERE "course"."user_id" = 2

I hope this will solve your problem

Upvotes: 1

Related Questions