CODEWITHSUNDEEP
sqlruby-on-railsdatabaseruby-on-rails-2
Raghuveer
Raghuveer

Reputation: 2638

Get values from 3 different tables based on specific condition

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

Upvotes: 0

Views: 145

Answers (2)

simonmenke
simonmenke

Reputation: 2880

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")

Upvotes: 2

digaomatias
digaomatias

Reputation: 1194

Your query looks ok.

Try changing from "join" to "left join" and see what is the result. 

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

left join users u on u.id = p.user_id

left join addresses a on a.addressable_id = p.id

The reason behind that is that maybe one of the ids are missing, thus removing whole result. If this is the case, you'll be able to see some columns with NULL value.

Upvotes: 1

Related Questions