如何使范围，将输出自定义数据

Question

我有型号Category和Transactions。

Category has_many transactions、Transaction belongs_to category。

我有Category:的范围

@relation = Category.all
@relation.joins(:transactions).where('transactions.created_at >= ?', 1.month.ago).
group('categories.id').order('SUM(transactions.debit_amount_cents) DESC')

它显示类别并按transactions.debit_amount_cents的总和对它们进行排序

我想显示其所有交易的金额以及每个类别。

类似：

id: 1,
name: "Category1",
all_amount: *some value* #like this

如何改进此范围？

Answer 1

class Category < ApplicationRecord
# remember that scope is just a widely abused syntactic sugar
# for writing class methods
def self.with_recent_transactions
joins(:transactions)
.where('transactions.created_at >= ?', 1.month.ago)
.select(
'categories.*',
'SUM(transactions.debit_amount_cents) AS total_amount'
)
.order('total_amount DESC')
.group('categories.id')

end
end

如果选择列或聚合并为其提供别名，则它将在生成的模型实例中可用。

Category.with_recent_transactions.each do |category|
puts "#{category.name}: #{category.total_amount}"
end

为了便于移植，您可以使用Arel而不是SQL字符串来编写它，这样可以避免对表名等内容进行硬编码：

class Category < ApplicationRecord
def self.with_recent_transactions
t = Transaction.arel_table
joins(:transactions)
.where(transactions: { created_at: Float::Infinity..1.month.ago })
.select(
arel_table[Arel.star]
t[:debit_amount_cents].sum.as('total_amount')
)
.order(total_amount: :desc) # use .order(t[:debit_amount_cents].sum) on Oracle
.group(:id) # categories.id on most adapters except TinyTDS
end
end

在Rails6.1(后端口到6.0x(中，您可以使用beginless范围来创建没有Float::Infinity:的GTE条件

.where(transactions: { created_at: ..1.month.ago })