提取满足Rails中模型功能的记录 [英] Extract records which satisfy a model function in Rails

问题描述

我在名为 CashTransaction 的模型中具有以下方法。

  def is_refundable？ 
 self.amount> self.total_refunded_amount 
结束
 
 def total_refunded_amount 
 self.refunds.sum（：amount）
结束
   true 的记录。 
 
 
 
我通过使用以下语句来做到这一点：
 
 
 
  CashTransaction.all.map {| x | x如果x.is_refundable？ }  
 
 
 
但是结果是 Array 。我正在寻找 ActiveRecord_Relation 对象，因为我需要对结果执行 join 。
 
 
 
我觉得我在这里错过了一些东西，因为它看起来并不那么困难。无论如何，这让我陷入困境。建设性的建议会很棒。 
 
 
 
注意：只是金额是 CashTransaction 列。 
 
 
 
 编辑 
 
 
 
在 SQL之后完成工作。如果我可以将其更改为 ORM ，它仍然可以完成工作。
  SELECT`cash_transactions`。* FROM`cash_transactions` INNER JOIN`refunds` on`refunds`退款在哪里refunds.cash_transaction_id = cash_transactions.id GROUP BY`cash_transaction_id`））; 
  
 共享进度 
 
 
 
我设法通过遵循ORM使其工作：
  CashTransaction 
 .joins（：refunds）
 .group（'cash_transactions.id'）
 .having（'cash_transactions.amount> sum（refunds.amount）'）
  
但是我实际上看到的是这样的：
  CashTransaction.joins（：退款（.is_refundable？：true）
  
其中 is_refundable？ code>是模型函数。最初，我认为将 is_refundable？设置为 attr_accesor 是可行的。但是我错了。
 
 
 
只是一个想法，可以使用 Arel 用一种优雅的方式解决问题。 / p> 
 
解决方案
有两种选择。
 
 
 
 1）完成，您要做什么已经开始（对于大量数据，这是非常低效的，因为所有数据在处理之前都会被存入内存）：
  CashTransaction.all.map（&：is_refundable？）＃与您编写的内容相同，但更短。 
  
获得ID：
  ids = CashTransaction.all.map（&：is_refundable？）。map（&：id）
  
现在要获取ActiveRecord关系：
  CashTransaction.where（id：ids）＃将返回一个关系
  
 2）将计算移至SQL：
  CashTransaction.where（'amount> total_refunded_amount'）
  
第二个选项以各种可能的方式更快，更有效。 
 
 
 
处理数据库时，请尝试在数据库级别进行处理，并尽可能减少Ruby的参与。
 
 
 
编辑
 
 
 
根据已编辑的问题，您将如何获得所需的结果：
  CashTransaction.joins（：refunds）.where（'amount> SUM（refunds.amount）'）
  
 
 
 
编辑＃2 
 
 
 
关于您的更新-我不太明白，为什么您锁定了 is_refundable ？作为实例方法，可以在查询中使用，这在AR中基本上是不可能的，但是.. 
 
 
 
我的建议是创建范围 is_refundable ：
  scope：is_refundable，-> {CashTransaction 
 .joins（：refunds）
 .group（'cash_transactions.id'）
 .having（'cash_transactions.amount> sum（refunds.amount）'）
 } 
  
现在，它的缩写形式为
  CashTransaction.is_refundable 
  
   
 
 
  CashTransaction.where（'is_refundable =？'，true）
  
 
I have following method in a model named CashTransaction.
def is_refundable?
    self.amount > self.total_refunded_amount
end

def total_refunded_amount
   self.refunds.sum(:amount)
end
Now I need to extract all the records which satisfy the above function i.e records which return true. 

I got that working by using following statement:

CashTransaction.all.map { |x| x if x.is_refundable? }

But the result is an Array. I am looking for ActiveRecord_Relation object as I need to perform join on the result.

I feel I am missing something here as it doesn't look that difficult. Anyways, it got me stuck. Constructive suggestions would be great. 

Note: Just amount is a CashTransaction column.

EDIT

Following SQL does the job. If I can change that to ORM, it will still do the job.
SELECT `cash_transactions`.* FROM `cash_transactions` INNER JOIN `refunds` ON `refunds`.`cash_transaction_id` = `cash_transactions`.`id` WHERE (cash_transactions.amount > (SELECT SUM(`amount`) FROM `refunds` WHERE refunds.cash_transaction_id = cash_transactions.id GROUP BY `cash_transaction_id`));
Sharing Progress

I managed to get it work by following ORM:
CashTransaction
  .joins(:refunds)
  .group('cash_transactions.id')
  .having('cash_transactions.amount > sum(refunds.amount)')
But what I was actually looking was something like:
CashTransaction.joins(:refunds).where(is_refundable? : true)
where is_refundable? being a model function. Initially I thought setting  is_refundable? as attr_accesor would work. But I was wrong.

Just a thought, can the problem be fixed in an elegant way using Arel.
解决方案
There are two options.

1) Finish, what you have started (which is extremely inefficient when it comes to bigger amount of data, since it all is taken into the memory before processing):
CashTransaction.all.map(&:is_refundable?) # is the same to what you've written, but shorter.
SO get the ids:
ids = CashTransaction.all.map(&:is_refundable?).map(&:id)
ANd now, to get ActiveRecord Relation:
CashTransaction.where(id: ids) # will return a relation
2) Move the calculation to SQL:
CashTransaction.where('amount > total_refunded_amount')
Second option is in every possible way faster and efficient. 

When you deal with database, try to process it on the database level, with smallest Ruby involvement possible.

EDIT

According to edited question here is how you would achieve the desired result:
CashTransaction.joins(:refunds).where('amount > SUM(refunds.amount)')


EDIT #2

As to your updates in question - I don't really understand, why you have latched onto is_refundable? as an instance method, which could be used in query, which is basically not possible in AR, but..

My suggestion is to create a scope is_refundable:
scope :is_refundable, -> { CashTransaction
  .joins(:refunds)
  .group('cash_transactions.id')
  .having('cash_transactions.amount > sum(refunds.amount)')
}
Now it is available in as short notation as 
CashTransaction.is_refundable
which is shorter and more clear than aimed
CashTransaction.where('is_refundable = ?', true)


                        
这篇关于提取满足Rails中模型功能的记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！