Get values from 3 different tables based on specific condition

Question

I have 3 tables in my database

1. User (id, username, email, pwd, etc ...)
2. Producer (id, user_id)
3. Address (first_name, city, addressable_id, etc)

Producer has link with User table(User.id = Producer.user_id) and Address table(p.id = Address.addressable_id)

Now I want to get all the producer addresses with his username

I am trying with following query but it is not giving the expected output

select u.login, p.id, a.city from producers p

join users u on u.id = p.user_id

join addresses a on a.addressable_id = p.id

My models and relationships

user.rb

class User < ActiveRecord::Base
  has_one :customer
  has_one :producer
end

producer.rb

class Producer < ActiveRecord::Base
  belongs_to :user
  belongs_to :updated_by_user, :class_name => "User", :foreign_key => "updated_by_user_id"

  has_one :address, :as => :addressable
  has_many :items  
end

address.rb

class Address < ActiveRecord::Base
  belongs_to :addressable, :polymorphic => true

  belongs_to :updated_by_user, :class_name => "User", :foreign_key => "updated_by_user_id"
end

Answer 1

you will want to use the #select method of ActiveRecord::Relation (docs).

Rails 3.X:

Producer.joins(:user, :address).select("users.login, producers.id, addresses.city")

Rails 2.X:

Producer.all(:joins => [:user, :address], :select => "users.login, producers.id, addresses.city")