Rails Active Record:通过多个自定义关联连接单个表,这些关联都指向同一列。

huangapple go评论106阅读模式
英文:

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:

  1. 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,

  1. class Order < ApplicationRecord
  2. belongs_to :advertiser, class_name: 'Account'
  3. belongs_to :facilitator, class_name: 'Account'
  4. 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)

  1. INNER JOIN accounts ON accounts.id = orders.facilitator_id
  2. OR accounts.id = orders.creator_id
  3. OR accounts.id = orders.advertiser_id

I tried the following,

  1. Order.joins(:advertiser, :facilitator, :creator)

but got the following result

  1. SELECT `orders`.*
  2. FROM `orders`
  3. INNER JOIN `accounts` ON `accounts`.`id` = `orders`.`advertiser_id`
  4. INNER JOIN `accounts` `facilitators_orders` ON `facilitators_orders`.`id` = `orders`.`facilitator_id`
  5. INNER JOIN `accounts` `creators_orders` ON `creators_orders`.`id` = `orders`.`creator_id`

答案1

得分: 0

你需要指定has_many关系的外键在Account模型中。

Order模型中:

  1. class Order < ApplicationRecord
  2. belongs_to :advertiser, class_name: 'Account'
  3. belongs_to :facilitator, class_name: 'Account'
  4. belongs_to :creator, class_name: 'Account'
  5. end

Account模型中:

  1. class Account < ApplicationRecord
  2. has_many :advertisers, foreign_key: 'advertiser_id', class_name: 'Order'
  3. has_many :facilitators, foreign_key: 'facilitator_id', class_name: 'Order'
  4. has_many :creators, foreign_key: 'creator_id', class_name: 'Order'
  5. 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.

  1. class Order < ApplicationRecord
  2. belongs_to :advertiser, class_name: 'Account'
  3. belongs_to :facilitator, class_name: 'Account'
  4. belongs_to :creator, class_name: 'Account'
  5. end

In Account model

  1. class Account < ApplicationRecord
  2. has_many :advertisers, foreign_key: 'advertiser_id', class_name: 'Order'
  3. has_many :facilitators, foreign_key: 'facilitator_id', class_name: 'Order'
  4. has_many :creators, foreign_key: 'creator_id', class_name: 'Order'
  5. end

This similar question might help too!
https://stackoverflow.com/questions/35104085/multiple-associations-with-the-same-table-rails

huangapple
  • 本文由 发表于 2023年5月22日 17:23:39
  • 转载请务必保留本文链接:https://go.coder-hub.com/76304716.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定