0

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
Raghuveer
  • 2,630
  • 3
  • 29
  • 59
  • Can you include the rest of the `addresses` table structure? You are currently joining `addressable_id` to the `producers` unique `id` - you should probably be joining a `producer_id` field on the `addresses` table to the `producers` unique `id`. –  Oct 31 '12 at 12:30
  • @MarkBannister No I'm not adding producer_id field on addresses table, addressable_id is equal to producer_id – Raghuveer Oct 31 '12 at 12:34
  • I wasn't saying you *should* add a producer_id field, I was saying I thought there was probably one **already there** - please reread what I wrote. It seems a bit pointless to have separate producers and addresses tables if they have the same key - remember, "the key, the whole key and nothing but the key". –  Oct 31 '12 at 13:04
  • @MarkBannister Hey **sorry** for my bad interpretation, Why I am adding address in different table is I have different kind of users like producer i have customer, employee etc., every user has his own address – Raghuveer Oct 31 '12 at 13:32

2 Answers2

2

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")
simonmenke
  • 2,819
  • 19
  • 28
1

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.

digaomatias
  • 1,164
  • 10
  • 21
  • btw in mysql `left join` is the same as `left outer join`. The outer is optional but sometimes clearer to read for some. – Michael Durrant Oct 31 '12 at 12:49
  • I always used just "left join" in sql server. Maybe that's why I think it's clearer than adding one more word to specify the same behavior. Just a matter of preference, I guess. – digaomatias Oct 31 '12 at 12:59