12 Gems of Christmas #12 – awesome_nested_set

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
  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.
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.

6 thoughts on “12 Gems of Christmas #12 – awesome_nested_set”

  1. A word of caution. This gem only works well for limited data sets. If you add this to a table which is constantly being added to or deleted from, this gem slows down your app.

    I had a comments model that used this to maintain parent/child comment structure. It started fine. Inserts and deletes in mere milliseconds. However, after about 100,000 comments, each insert took 5s, each delete took 25s…

    I ended up removing this gem, rolled my own basic nested set code, and managed to get times back to mere milliseconds.

  2. I will concur with Kieran. For large collections, doing anything that modifies your nested set takes a long time, and is also prone to error when multiple operations are being handled simultaneously. It is very common to end up with “holes” in your nested set, which can be very damaging. Use with caution.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>