Category trees are standard e-commerce functionality, allowing the user to filter their results when searching for products. Here’s a category tree from

So if you have a table which holds those categories, how do you write a SQL query which loads a given category PLUS all child categories below it? The answer is that it’s extremely difficult/impossible with standard SQL. Oracle can do it with their SQL “connect by” extension or you can change your tree structure into a nested set.


class Category < ActiveRecord::Base
  has_many :products
  belongs_to :parent, :class_name => 'Category'
  attr_accessible :name, :parent_id, :parent

class Product < ActiveRecord::Base
  belongs_to :category
  attr_accessible :category_id, :name, :category

cat = Category.find_by_name("Electronics")
# look up all children in one query
subcats = cat.self_and_descendants

# Find all products within Electronics subtree with one query.
# A bit messy.

awesome_nested_set is missing some critical documentation, the README explains how to set it up but doesn’t cover queries and scopes at all. I couldn’t find a way to do something cleaner like so without rolling my own scope:

cat = Category.find_by_name("Electronics")
products =



Tomorrow I’ll show you a great little gem for adding metrics to your Ruby code.