How to make scope, that will output custom data?

Question

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?

max · Accepted Answer · 2020-11-19T15:45:25.643

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 })

It works. Thank you! But for some reason, when i try `Category.with_recent_transactions.count` i get error `PG::SyntaxError: ERROR: syntax error at or near "AS"` . Perhaps you know what the problem might be? — SsPay, Nov 19 '20 at 16:58
Yeah don't use `.count`. `.count` clobbers the entire select clause so that the only thing returned is a count of the number of rows. If you're selecting something like an aggregate and referencing it somewhere else like in the `WHERE`, `ORDER` or `HAVING` clause `.count` will break it. Use `size` or `length` instead or a different query if you want to use `.count`. See https://stackoverflow.com/questions/6083219/activerecord-size-vs-count — max, Nov 19 '20 at 17:05

How to make scope, that will output custom data?

1 Answers1