CODEWITHSUNDEEP
ruby-on-railsactiverecord
SsPay
SsPay

Reputation: 191

How to make scope, that will output custom data?

I have models Category and Transactions.

Category has_many transactions, Transaction belongs_to category.

And i have scope for Category:

@relation = Category.all

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

It displays categories and sorts them by sum of transactions.debit_amount_cents

I want to display the amount for all its transactions along with each category.

Like:

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

How can I improve this scope?

Upvotes: 0

Views: 35

Answers (1)

max
max

Reputation: 102001

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

If you select a column or an aggregate and give it an alias it will be available on the resulting model instances.

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

For portability you can write this with Arel instead of SQL strings which avoids hardcoding stuff like table names:

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

In Rails 6.1 (backported to 6.0x) you can use beginless ranges to create GTE conditions without Float::Infinity:

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

Upvotes: 2

Related Questions