How can I select a SUM in a query using Rails 3 / MetaSearch?

ruby-on-rails-3 activerecord meta-search
KTY picture KTY · May 1, 2011 · Viewed 11.8k times · Source

I have a Rails 3 application where my model includes Owners and Properties, each Owner representing a person or institution that owns one or more properties.

I would like to be able to search my database (SQLite3) and return the results grouped by owner. For each group, I would like to display:

-The owner's name (I can do this easily) -The total number of properties belonging to this owner that meet the search conditions (i.e., a count). -The total value of all the properties counted in the previous column (i.e., a sum).

Owner has_many Properties, and Property belongs_to Owner. Also, "value" is an attribute for Property.

I am using the MetaSearch gem, and I can get it to return a collection of properties correctly. I can also get it to group the results by owner, but I can't figure out how to display the number of properties and their summed value.

Here is the code to return the list of properties:

@search = Property.group("owner_id").search(params[:search])

I have tried adding a .select to the chain like this:

@search = Property.select("SUM(value) as mysum").group("owner_id").search(params[:search])

But I can't access this sum when I try. Does anyone know of an efficient way of handling this situation?

Answer

John Hinnegan picture John Hinnegan · Sep 3, 2012

I realize this is old, but it's high in google search results.

You can simply call sum. Looking at your example, the following is approx what you're looking for:

Property.where(SEARCH_VALUES).group(:owner_id).sum(:value)

http://guides.rubyonrails.org/active_record_querying.html#sum

Related questions

How do you manually execute SQL commands in Ruby On Rails using NuoDB

I'm trying to manually execute SQL commands so I can access procedures in NuoDB. I'm using Ruby on Rails and I'm using the following command: ActiveRecord::Base.connection.execute("SQL query") The "SQL query" could be any SQL command. Like …

Read More
Check if record exists from controller in Rails

In my app a User can create a Business. When they trigger the index action in my BusinessesController I want to check if a Business is related to the current_user.id: If yes: display the business. If no: redirect …

Read More
Can you do greater than comparison on a date in a Rails 3 search?

I have this search in Rails 3: Note.where(:user_id => current_user.id, :notetype => p[:note_type], :date => p[:date]).order('date ASC, created_at ASC') But I need the :date => p[:date] condition to be …

Read More