CODEWITHSUNDEEP
ruby-on-railsactiverecord
JohnGood
JohnGood

Reputation: 15

Rails How to find record when searching on association of association

I have three tables: users, cars, and mechanics.

associations:

I want to find users with cars repaired by a specific mechanic, so I'm doing something like this:

User.joins(:cars).where('cars.color = ? AND cars.type = ? AND cars.mechanic.name = ?', 'green', 'estate', 'paul')

The trick is that I don't know how to write this cars.mechanic.name part.

Upvotes: 1

Views: 75

Answers (3)

Anatoly
Anatoly

Reputation: 15530

An association has_many can be extended with through option, see API docs for more details. Specific to your question Car model belongs to both User and Mechanic, so this should help you:

class User < ActiveRecord::Base
  has_many :cars
  has_many :mechanics, through: :cars
end


class Mechanic < ActiveRecord::Base
  has_many :cars
  has_many :users, through: :cars
end


class Car < ActiveRecord::Base
  belongs_to :user
  belongs_to :mechanic
end


class MechanicsController < ActionController::Base
  def show
    @mechanic = Mechanic.find_by(name: params[:name])
    @users    = @mechanic.users.includes(:cars)
  end
end

Upvotes: 0

Sachin R
Sachin R

Reputation: 11876

User
  .joins( :cars => :mechanic )
  .where( :cars     => {:color => "green", :type => "estate"}, 
          :mechanic => {:name => "paul"} )

Try this may be this will work.

Upvotes: 2

Shadwell
Shadwell

Reputation: 34774

You need to join in mechanic as well and then you can query on it:

q = User.joins(cars: :mechanic)
q = q.where('cars.color = ? and cars.type = ?', 'green', 'estate')
q = q.where('mechanics.name = ?', 'paul')

You'll need to watch out though because you are then potentially returning multiple User instances. If a user has two cars (two green estates) in this example then they will be returned twice by that query. Yo get round that you probably want to add a distinct on the query too to only return a user once regardless of how many of their green estates Paul may be attending to. 

q = q.distinct

Upvotes: 0

Related Questions