英文:
Rails Active Record: Joining single table by multiple custom associations pointing to same column
问题
You can achieve the desired SQL query using Active Record by chaining multiple joins with conditions like this:
Order.joins("INNER JOIN accounts ON accounts.id = orders.facilitator_id OR accounts.id = orders.creator_id OR accounts.id = orders.advertiser_id")
This will generate the SQL query with the specified INNER JOIN conditions.
英文:
I am trying to join to the same table by multiple custom associations.
My model has the following associations,
class Order < ApplicationRecord
belongs_to :advertiser, class_name: 'Account'
belongs_to :facilitator, class_name: 'Account'
belongs_to :creator, class_name: 'Account'
how can I create the following SQL query using Active Record and/or Arel? (beyond just providing an SQL string directly)
INNER JOIN accounts ON accounts.id = orders.facilitator_id
OR accounts.id = orders.creator_id
OR accounts.id = orders.advertiser_id
I tried the following,
Order.joins(:advertiser, :facilitator, :creator)
but got the following result
SELECT `orders`.*
FROM `orders`
INNER JOIN `accounts` ON `accounts`.`id` = `orders`.`advertiser_id`
INNER JOIN `accounts` `facilitators_orders` ON `facilitators_orders`.`id` = `orders`.`facilitator_id`
INNER JOIN `accounts` `creators_orders` ON `creators_orders`.`id` = `orders`.`creator_id`
答案1
得分: 0
你需要指定has_many关系的外键在Account模型中。
在Order模型中：
class Order < ApplicationRecord
belongs_to :advertiser, class_name: 'Account'
belongs_to :facilitator, class_name: 'Account'
belongs_to :creator, class_name: 'Account'
end
在Account模型中：
class Account < ApplicationRecord
has_many :advertisers, foreign_key: 'advertiser_id', class_name: 'Order'
has_many :facilitators, foreign_key: 'facilitator_id', class_name: 'Order'
has_many :creators, foreign_key: 'creator_id', class_name: 'Order'
end
这个类似的问题也可能有所帮助！
https://stackoverflow.com/questions/35104085/multiple-associations-with-the-same-table-rails
英文:
You need to specify foreign keys of has_many relations Account model.
class Order < ApplicationRecord
belongs_to :advertiser, class_name: 'Account'
belongs_to :facilitator, class_name: 'Account'
belongs_to :creator, class_name: 'Account'
end
In Account model
class Account < ApplicationRecord
has_many :advertisers, foreign_key: 'advertiser_id', class_name: 'Order'
has_many :facilitators, foreign_key: 'facilitator_id', class_name: 'Order'
has_many :creators, foreign_key: 'creator_id', class_name: 'Order'
end
This similar question might help too!
https://stackoverflow.com/questions/35104085/multiple-associations-with-the-same-table-rails
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论