Rails meta_search gem: sort by count of an associated model

Question

I'm using meta_search to sort columns in a table. One of my table columns is a count of the associated records for a particular model.

Basically it's this:

class Shop < ActiveRecord::Base
  has_many :inventory_records

  def current_inventory_count
    inventory_records.where(:current => true).count
  end
end

class InventoryRecord < ActiveRecord::Base
  belongs_to :shop

  #has a "current" boolean on this which I want to filter by as well
end

In my Shop#index view I have a table that lists out the current_inventory_count for each Shop. Is there anyway to use meta_search to order the shops by this count?

I can't use my current_inventory_count method as meta_search can only use custom methods that return an ActiveRecord::Relation type.

The only way I can think about doing this is to do some custom SQL which includes the count in a "virtual" column and do the sorting by this column. I'm not sure if that's even possible.

Any Ideas?

I'm using Rails 3.0.3 and the latest meta_search.

Copier

would my best bet be to just add a "current_inventory_records_count" db column to Shops that I just make sure is always correct using before_save's? – Copier

Answer 1

To add extra columns to a result set...

In Shop.rb ..

scope :add_count_col, joins(:inventory_records).where(:current=>true).select("shops.*, count(DISTINCT inventory_records.id) as numirecs").group('shops.id')

scope :sort_by_numirecs_asc, order("numirecs ASC")
scope :sort_by_numirecs_desc, order("numirecs DESC")

In shops_controller.rb index method

@search = Shop.add_count_col.search(params[:search])
#etc.

In index.html.erb

<%= sort_link @search, :numirecs, "Inventory Records" %>

Found the sort_by__asc reference here: http://metautonomo.us/2010/11/21/metasearch-metawhere-and-rails-3-0-3/

Knockknee

Answer 2

Rails has a built-in solution for this called counter_cache

Create a table column named "inventory_records_count" on your shops table.

class Shop < ActiveRecord::Base
  has_many :inventory_records, :counter_cache => true
end

http://asciicasts.com/episodes/23-counter-cache-column

Sheikdom

Thanks, that's good to know, but I want to count only the inventory records that are marked as "current". – Copier

has_many :inventory_records, :conditions => { :current => true }, :counter_cache => true – Sheikdom