mysql - 改变Rails的belongs_to, has_many默认的Select (Where)语句

Question

我正在尝试找出一种方法来改变“belongs_to”和“has_many”关系，从而在两者上执行正常和预期的(见下文)行为...

'SELECT `transactions`.* FROM `transactions` WHERE `transactions`.`account_id` = 2'

到

'SELECT `transactions`.* FROM `transactions` WHERE `transactions`.`account_id` = 2' OR `transactions`.`transfer_id` = 3'

当前设置...

class Account < ActiveRecord::Base
  has_many :transactions
end

class Transaction < ActiveRecord::Base
  belongs_to :account
end

class CreateTransactions < ActiveRecord::Migration
  def change
    create_table :transactions do |t|
      t.string        :name,
      t.references    :account,
      t.timestamps
    end
  end
end

尝试的解决方案...

class Account < ActiveRecord::Base
  has_many :transactions, :class_name => "Transaction", :finder_sql => ->(record) do
    record = self if(record.nil?)
    "SELECT * FROM transactions WHERE transactions.account_id = #{record.id} OR transactions.transfer_id = #{record.id}"
  end
end

class Transaction < ActiveRecord::Base
  belongs_to :transfer, :class_name => "Account", :foreign_key => 'transfer_id'
end

class CreateTransactions < ActiveRecord::Migration
  def change
    create_table :transactions do |t|
      t.string        :name,
      t.references    :account,
      t.integer       :transfer_id,
      t.timestamps
    end
  end
end

问题是，如何更改默认的 WHERE 以包含 OR。在不使用foreign_key的情况下向has_many添加范围或添加似乎会产生...

'SELECT transactions.* FROM transactions WHERE transactions.account_id = 2' (AND 交易.transfer_id = 3')

事实上，我所有使用 Rails 传统方式的尝试似乎都产生了上述结果。

弄清楚如何更改默认关系的重要性是为了确保所有查找 account_id 的 select 语句也会查看transfer_id。

我们将非常感谢您的帮助。

Answer 1

抱歉，但我不相信 has_many 支持您正在寻找的内容。按照惯例，关联应该引用所有给定的键。在您的情况下，您正在尝试引用几个键之一。

最简单的解决方案是创建 2 个独立的关联，然后在需要时合并它们:

class Account < ActiveRecord::Base
  has_many :transactions # this will use the 'account_id' field by convention
  has_many :transfer_transactions, :class_name => "Transaction", :foreign_key => :transfer_id
  def all_transactions
    (transactions + transfer_transactions).uniq
  end
end

这里的缺点是您要进行 2 次数据库调用，并且 #all_transactions 方法将无法用作嵌套关联。

另一种可能性是修改您的架构并为每个物理交易创建 2 个交易条目，并标记借方/贷方方向。无论如何，这更符合复式记账法:

class Account < ActiveRecord::Base
  has_many :transactions

  def credit_transactions
    transactions.credit
  end

  def debit_transactions
    transactions.debit
  end
end

class Transaction < ActiveRecord::Base
  belongs_to :account
  # add some validation to make sure you set the debit/credit flag

  scope :credit, where(:credit => true)
  scope :debit, where(:credit => false)

  # some virtual accessors for 'debit'
  def debit=(direction)
    credit = !direction
  end
  def debit?
    !credit?
  end
end

class CreateTransactions < ActiveRecord::Migration
  def change
    create_table :transactions do |t|
      t.string        :name
      t.references    :account
      t.boolean :credit, :nil => false
      t.timestamps
    end
  end
end

您可能想要研究的另一个选项是“Squeel” gem 。它基本上是一个用于 SQL 的 ruby​​ DSL，并允许各种复杂的查询。不过，我不确定它是否适用于 has_many 声明。