rails named scope issues

Question

I have two named scopes... both which work separately, but when combined do not work.

  named_scope :total, :select => "COUNT(*) as days, AVG(price) as price, SUM(price) AS total", :group => :parent_id
  named_scope :currency, lambda { |code| { :select => "*, price * #{(CurrencyRate.get_rate("USD", (code ||= "USD") ,1))} AS price" } }

Example:

c=Booking.total.currency("EUR").find_all_by_parent_id(63)

The total named scope works but not the currency...

c=Booking.currency("EUR").total.find_all_by_parent_id(63)

The currency named scope works but not the totals...

Any ideas?

Answer 1

Please make sure(via log) that the resulting query of the combined call is the sum of both queries, if it's, then the combination of the 2 queries is an empty set and you need to reconsider them both working together. if it's not, then please paste your model...

Answer 2

I think you have a couple of problems.

• Both scopes define "as price" and they conflict, this will produce invalid SQL

• One scope has a group clause, but the other doesn't have an aggregate function on a non grouped attribute in the select clause. This will result in invalid SQL.

Instead of a scope that complicates your sql, consider using a computed attribute on your booking model.

class Booking
    def currency(code="USD")
        price * CurrencyRate.get_rate(code) ,1)
    end
end

Now you can:

c=Booking.total.find_all_by_parent_id(63)
for booking in c
    puts booking.currency("EUR")
end

If you must use a scope, consider combining them. I realize you probably don't want to create named scopes for each permutation, but I think something has to change. You can create a named_scope :currency_total

named_scope :currency_total, lambda { |code| {:select => "COUNT(*) as days, 
           AVG(price * #{(CurrencyRate.get_rate("USD", (code ||= "USD") ,1))}) as price, 
           SUM(price * #{(CurrencyRate.get_rate("USD", (code ||= "USD") ,1))}) AS total", 
           :group => :parent_id }}

Answer 3

Sadly named_scope doesn't chain anything else than conditions successfully at the moment.

:select will be set by only one of the named scopes. Therefore you should go with jrhicks approach.

Btw: That's why you should start every :select => "model_name.*, ... so that Rails gets the model's fields. You will run into other problems/errors otherwise.