How to list products from all subcategories?

Rails
1

Is there an easy way to list latest 10 products from all subcategories
of a parent category? The max depth of the category tree is 5 levels.

Models:

Category
belongs_to :category
has_many :categories
has_many :products

Product
belongs_to :category

I want something like:

@category = Category.find(435)
@products = @category.latest_ten_products_in_subcategories

Is this possible even in pure SQL (SQLite3 or MySQL 5) without multiple
queries and merging & sorting the results after that?

Thanks

2

Does this address your question?

http://lordmatt.co.uk/item/915/

On Aug 14, 3:41 pm, Jari J. [email protected]

3

Mukund wrote:

Does this address your question?
http://lordmatt.co.uk/item/915/

Thanks, based on that article I created this method:

class Product < ActiveRecord::Base

def self.latest_by_category(id)
  db = ActiveRecord::Base.connection
  query = "DROP VIEW IF EXISTS categories_#{id}"
  db.execute(query)

  query = "
    CREATE TEMPORARY VIEW categories_#{id} AS
    SELECT COALESCE(c5.id, c4.id, c3.id, c2.id, c1.id) AS id
    FROM categories c1
    LEFT JOIN categories c2 ON c2.category_id = c1.id
    LEFT JOIN categories c3 ON c3.category_id = c2.id
    LEFT JOIN categories c4 ON c4.category_id = c3.id
    LEFT JOIN categories c5 ON c5.category_id = c4.id
    WHERE c1.id = #{id}
  "
  db.execute(query)

  find(:all, :select => "p.*", :from => "categories_#{id} c",
             :joins  => "LEFT JOIN products p ON p.category_id =

c.id",
:order => “created_at DESC”, :limit => 10)
end

end

It can be used like this:

<% for product in Product.latest_by_category(@category.id) %>
<%= product.name %>

<% end %>

That ActiveRecord method works at least with SQLite3, and I think it
should work fine with MySQL too (not tested though). I had to use SQL
views to store temporary data because SQLite3 doesn’t support nested SQL
statements.