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/

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

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.

This forum is not affiliated to the Ruby language, Ruby on Rails framework, nor any Ruby applications discussed here.

| Privacy Policy | Terms of Service | Remote Ruby Jobs