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.

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

Does this address your question?

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

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.