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.
Tomorrow I’ll show you a great little gem for adding metrics to your Ruby code.