根据具体情况从3个不同的表中获取值

I have 3 tables in my database

我的数据库中有3个表

User (id, username, email, pwd, etc ...)

用户(id,用户名,电子邮件,密码等...)

Producer (id, user_id)

制片人(id,user_id)

Address (first_name, city, addressable_id, etc)

地址(first_name,city,addressable_id等)

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

Producer与User表(User.id = Producer.user_id)和Address表(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

2 个解决方案

#1

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

您将需要使用ActiveRecord :: Relation(docs)的#select方法。

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

#2

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.

其背后的原因是可能缺少其中一个ID,从而删除了整个结果。如果是这种情况,您将能够看到一些具有NULL值的列。

#1