ruby-on-rails Rails多连接查询

vkc1a9a2 于 5个月前发布在 Ruby

关注(0)|答案(2)|浏览(88)

我有下面的模型结构

class Transaction
  belongs_to :company
  belongs_to :user
end

class Company
  belongs_to :address
end

class User
  belongs_to :address
end

classAddress
 has_many :companies
 has_many :users
end

字符串
我想查询所有的交易，并获得公司地址和用户地址

Transaction.joins([company: :address], [user: :address])
    .select('company.addresses.line_1 as company_add_line1', 
       'user.addresses.line_1 as user_add_line1')

型
对于语法“company.addresses.line_1”，我得到以下错误

ActiveRecord::StatementInvalid: Mysql2::Error: Unknown column 'company.addresses.line_1' in 'field list'

型
如何实现这一点？

ruby-on-rails

来源：https://stackoverflow.com/questions/77613849/rails-multiple-join-query

2条答案

按热度按时间

bnl4lu3b1#

我注意到，您的用户和公司都应该have_many :transactions
您的语法失败B/c您有类似ActiveRecord的语法（例如，SQL片段中的companies.addresses.line1）
你需要这样的东西...

select_user_add = <<-SEL
  (select line1 
   from addresses
   joins users
     on users.address_id = addresses.id
   where transaction.user_id = users.id ) as user_add_line1
  SEL

select_company_add = <<-SEL
  (select line1 
   from addresses
   joins companies
     on companies.address_id = addresses.id
   where transaction.user_id = companies.id ) as company_add_line1
  SEL

Transaction.select("transactions.*, #{select_user_add}, #{select_company_add")

字符串

赞(0）回复(0）举报 5个月前

uxh89sit2#

您没有在公司和用户模型中提到has_many：transactions。
交叉检查字段与您的模式字段，这将工作。

transaction_id = 1  # actual transaction ID

transaction_data = Transaction.joins(company: :address, user: :address)
                           .where(transactions: { id: transaction_id })
                           .select('transactions.*, companies.*, users.*',
                                   'company_addresses.line_1 as company_add_line1',
                                   'user_addresses.line_1 as user_add_line1')
                           .first

字符串

赞(0）回复(0）举报 5个月前

我来回答

ruby-on-rails Rails多连接查询

2条答案

相关问题

热门标签

最新问答