# How to list products from all subcategories?

**URL:** <https://www.ruby-forum.com/t/how-to-list-products-from-all-subcategories/145838>\
**Category:** Rails\
**Created:** [August 14, 2008, 12:41pm UTC](https://www.ruby-forum.com/t/how-to-list-products-from-all-subcategories/145838 "2008-08-14T12:41:14Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![jarijokinen](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/aca169/32.png) [@jarijokinen](https://www.ruby-forum.com/u/jarijokinen)\
**Post date:** [August 14, 2008, 12:41pm UTC](https://www.ruby-forum.com/t/how-to-list-products-from-all-subcategories/145838/1 "2008-08-14T12:41:14Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![jarijokinen](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/aca169/32.png) [@jarijokinen](https://www.ruby-forum.com/u/jarijokinen)\
**Post date:** [August 14, 2008, 1:04pm UTC](https://www.ruby-forum.com/t/how-to-list-products-from-all-subcategories/145838/2 "2008-08-14T13:04:43Z")

</div>

Does this address your question?

[http://lordmatt.co.uk/item/915/](http://lordmatt.co.uk/item/915/)

On Aug 14, 3:41 pm, Jari J. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid)

---

<div class="post-metadata">

**Author:** ![jarijokinen](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/aca169/32.png) [@jarijokinen](https://www.ruby-forum.com/u/jarijokinen)\
**Post date:** [August 14, 2008, 5:56pm UTC](https://www.ruby-forum.com/t/how-to-list-products-from-all-subcategories/145838/3 "2008-08-14T17:56:54Z")

</div>

Mukund wrote:

> Does this address your question?  
> [http://lordmatt.co.uk/item/915/](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.