12 Gems of Christmas #12 – awesome_nested_set

2012-12-01

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

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.

The awesome_nested_set gem adds really useful tree functionality to your category table. Need to query for all products in a given category or below in the category tree?

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

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

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.
products = Product.active.joins(:category).where('categories.lft > ? and categories.lft <= ?', cat.lft, cat.rgt)

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 = Product.active.within_category(cat)

Is this possible? Could the category association provide some built-in nested set scopes? Nevertheless, adding a few custom scopes is a small price to pay: nested sets turn a very hard SQL problem into something easily solved.

UPDATE: I learned of two nice alternatives to awesome_nested_set: ancestry and closure_tree.

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