How to list products from all subcategories?

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.


    belongs_to :category
    has_many :categories
    has_many :products

    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?


Does this address your question?

Mukund wrote:

Does this address your question?

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}"

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

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


It can be used like this:

  <% for product in Product.latest_by_category( %>
    <%= %><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