Forum: Ruby on Rails How to do select with sum?

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
miga (Guest)
on 2009-05-01 11:42
(Received via mailing list)
Is there a way to rewrite the following Model method avoiding
connection.select?

class LineOrder < ActivateRecord::Base
belongs_to :auction
belongs_to :operation

...
def self.amounts_by_year(search)
  search_company = ""
  if search
     search_company = "inner join auctions on
line_orders.auction_id=auctions.id where company LIKE "
       + "'%#{search}%'"
  end
  connection.select_all("select year(date) as YD,
    sum(if(operation_id != 2, gross_price, NULL)) as BGP,
    sum(if(operation_id != 2, costs, NULL)) as BC,
    sum(if(operation_id != 2, net_price, NULL)) as BNP,
    sum(if(operation_id = 2, gross_price, NULL)) as SGP,
    sum(if(operation_id = 2, costs, NULL)) as SC,
    sum(if(operation_id = 2, net_price, NULL)) as SNP
    from line_orders " + "#{search_company} " + "group by year(date)")
end

To fix the idea:

The method is used in the index view to show at user's will the
amounts by year either of a given auction or of all auctions,
depending if the user has chosen to restrict the list to a particular
auction or not.

search is then the variable which is passed from the view to the model
to get the scope on which the method will be applied.

company is a field of the auction Model.

gross_price, costs, net_price are entered unsigned for user's
convenience. Hence they need to be separated afterwards depending on
the operation_id.
Steve Shepherd (Guest)
on 2009-05-02 03:44
(Received via mailing list)
I am interested in this also.
Sorry don't have any ideas as I am to new to RoR myself
This topic is locked and can not be replied to.