How to list products from all subcategories?

rubyonrails-talk
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/

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 %><br />   <% 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.