Rails: How to filter and sort products?

Product has the following attributes:

• shop
• model
• price

Model belongs to:

• brand

Given @products, like:

Shop    Brand    Model    Price
----    -----    -----    -----
Ebay    Sony     S-100    500
Ebay    Sony     S-200    1000        (in Target it's cheaper)
Ebay    Dell     D-55     300
Target  Sony     S-100    600         (in Ebay it's cheaper)
Target  Sony     S-200    900
Amazon  Dell     D-33     100

I would like to filter only those with the lowest price, i.e. I expect to get:

Shop    Brand    Model    Price
----    -----    -----    -----
Ebay    Sony     S-100    500
Ebay    Dell     D-55     300
Target  Sony     S-200    900
Amazon  开发者_JAVA技巧Dell     D-33     100

and preferably to sort by Brand and then by Model:

Shop    Brand    Model    Price
----    -----    -----    -----
Ebay    Sony     S-100    500
Target  Sony     S-200    900
Amazon  Dell     D-33     100
Ebay    Dell     D-55     300

How could I do this ?

You should be able to do this in a single query by joining the tables, grouping to only get one result per model and ordering to come back in the order you want. The following should be along the lines of what you need:

Product.joins(:model => :brand).group("models.name").order("products.price, brands.name, models.name")

I've guessed your model and brand tables have a column called name or something which is what the model name and brand name is stored in.

Would you be happy with database specific sorting?

@products.order('brand_id, price')

EDIT: Filter and order

EDIT2: Not index, but group by the model

EDIT3: Sort on brand first and then model

EDIT4: Dont stop until there's scope for improvement. A better multifield sort

def lowest_priced(products)
  # All products are grouped by the model (key is model, value is array of products belonging to that model)
  per_model = products.group_by{|x|x.model}
  # Hash of lowest products per model
  lowest = {}
  per_model.keys.each do |model|
    # Find lowest priced product for the model
    lowest[model] = per_model[model].min_by{|p| p.price}
  end
  # Sort the values of the hash
  return lowest.values.sort_by{|p| [p.model.brand,p.model]}
end

You would ideally refactor the code above so that each function has only one responsibility.

Use searchlogic gem. it generates a lot of scopes for searching and ordering. e.g.

Product.search(:order => :ascend_by_model)