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

Uprise

Answer 1

To avoid duplicates you can try this,

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

Kitchen

Thanks! Your solution seems perfect for what I need to do. I really didn't know it was possible to use "group" on a specific attribute and use it like that... You always learn new things in the magical world of Rails! :) – Uprise

Answer 2

Maybe try this

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

This will fetch duplicated values as well. Let's say you wish to fetch names, maybe you could try this

products.map(&:name).uniq

Encampment

Both answers worked, but with the one of shweta, it's easier to access the products' attributes. I vote up because your solution works fine! :) – Uprise

Although grouping looks like an elegant solution, I'm not entirely convinced if it's scalable. Often, during development, I realize that i need over 2-3 parameters from the same table. In this case, in my opinion, it might be wise to fetch all your columns in an array & use it throughout your page. It's only one query :) If you call group, a different query would have to be fired every time you wish to fetch a new attribute. That being said, if it's only the name you want, group is definitely a more elegant solution. Would love it if someone could critique my thought process. – Encampment

It's for a huge table where I have to access most of the products' attributes. Is there a way to loop the query through the attributes? And when two different products have the same name, the products.man(&:name).uniq wouldn't erase also those duplicated names? With the grouping solution I can group by products' ID and get names, so duplicated names won't be erased. I also find this solution: Product.joins(:categories).select(“distinct(products.id)”) from the Rails guide: guides.rubyonrails.org/… but I only get the "id" column... – Uprise

Ah. I didn't think about many products having the same name. In that case distinct looks nice. Map the resulting recordset to get the array you need. :) – Encampment

Updated my answer. Wonder if this looks better. – Encampment

You're right about the fact that it's easier to add properties. But I see one drawback: you'll then have to set "priority" for each product/category couple, what I wanted at the first place was to set a priority per category. :) But, this is not really the right approach I think for online shops... so yours is better. And with that solution, what is the best way to get rid of duplicated products? I'll update my question so that you can see what I really want to do. :) Thanks again for your help and the time you take to improve the solution! You're awesome! :) – Uprise

I remembered something else: in fact, I do want priority per category. Because I also want to be able to sort categories per priority. And having the priority on the join table wouldn't allow me to do that (unless I sum priorities but that not the feature I want :) ). – Uprise

let us continue this discussion in chat – Encampment

I went to the chat and said hello but you left... :( – Uprise

Oh :). Since the comments are not adding any value to the other Stackoverflow users, i thought maybe we could discuss further in chat. I'll update my answer to avoid any confusion. – Encampment