Rails - how to order a model by its has many association?

Question

I have two models:

#Product
class Product < ActiveRecord::Base
  has_and_belongs_to_many :categories
  attr_accessible :name
  ...
end

#Category
class Category < ActiveRecord::Base
  has_and_belongs_to_many :products, :order => "name ASC"

  attr_accessible :name, :priority
end

And I'd like to order my products by the highest priority category and then by products' name

For example I have: 3 categories:

• Sweets, priority = 3
• Fruits, priority = 1
• Desserts, priority = 2

3 products:

• Chocolate Ice Cream, which has desserts and sweets categories,
• Cookies, which has desserts and sweets category,
• Kiwi, which has sweets, fruits and desserts categories

And I'd like them to be ordered like that:

• Kiwi (first because Fruits is highest priority)
• Chocolate Ice Cream (second because chocolate comes before cookies and they both have categories with same priority)
• Cookies

How may I do that in Rails? I don't want my products to be duplicated, I know it would be easy to do something like:

Category.order("priority ASC").each do |cat|
  cat.products.order("name ASC").each do |product|
  end
end

But in that case Kiwi, Chocolate Ice Cream and Cookies would be duplicated because they all have several categories. Is there a simple way to remove duplicates? Or is there a way to order products directly by category highest priority?

Edit: more details about what I want to achieve

What I want in fact, is a huge table where, at the left, I have all the products (and only one line per unique product) sorted by categories... So that I can have something like this:

• Category - Product
• Fruit - Banana
• Fruit - Apple
• Fruit - Kiwi
• Desserts - Chocolate Ice Cream
• Desserts - Cookies
• Sweet - Chocolate Candy
• Sweet - Apple Candy
• ...

See? Even if a fruit is a dessert and sweet, I want it to appears only one time in this table. And I want products to appear in their "most important" category (that why I thought about "priority").

As this huge table will be used to edit products, I want to be able to easily access products' attributes (there'll be one column per attribute). So I really need to do as minimum database requests as possible.

---

Thanks to anyone who may help me! Kulgar

Answer 1

To avoid duplicates you can try this,

Product.joins(:categories).group("products.name").order("categories.priority ASC, products.name ASC")