Efficiently building collections of nested sets

Rails
1

This is question deals with the common example where Category has_many
Products. (We’ll use Species instead of Products here.)

Assume Category is a nested class. Let’s use this as the example:

[Categories]
Animals (id 1, parent_id=nil)
-Mammals (id 2, parent_id=1)
–Rodents (id 3, parent_id=2)
-Birds (id 4, parent_id=1)

[Species]
Dog (category_id 2)
Cat (category_id 2)
Mouse (category_id 3)
Rabbit (category_id 3)
Duck (category_id 4)

I want to be able to perform a select at Animals and get all of the
Species below it, and also be able to select from Birds and just get
Duck. It’s also necessary to sort the resultant collection.

This seems like a common problem and I’m overlooking a simple
solution. Right now, though, I’m thinking I need to build up a query
with a lot of ORs in the WHERE clause (e.g. to get mammals and the
children I’d need :condition => “category_id = 2 OR category_id =
3”). This doesn’t feel very rails-like.

I’m not opposed to restructuring the database at this point, if that
makes solving this problem easier.

Thanks in advance.
Dave

2

DGerton wrote:

This is question deals with the common example where Category has_many
Products. (We’ll use Species instead of Products here.)

Assume Category is a nested class. Let’s use this as the example:

[Categories]
Animals (id 1, parent_id=nil)
-Mammals (id 2, parent_id=1)
–Rodents (id 3, parent_id=2)
-Birds (id 4, parent_id=1)

[Species]
Dog (category_id 2)
Cat (category_id 2)
Mouse (category_id 3)
Rabbit (category_id 3)
Duck (category_id 4)

I want to be able to perform a select at Animals and get all of the
Species below it, and also be able to select from Birds and just get
Duck. It’s also necessary to sort the resultant collection.

This seems like a common problem and I’m overlooking a simple
solution. Right now, though, I’m thinking I need to build up a query
with a lot of ORs in the WHERE clause (e.g. to get mammals and the
children I’d need :condition => “category_id = 2 OR category_id =
3”). This doesn’t feel very rails-like.

I’m not opposed to restructuring the database at this point, if that
makes solving this problem easier.

You’re using an adjacency-list tree model right now, where each node
only stores its parent ID. This has the problem you’ve discovered: you
can’t get all descendants to arbitrary depths with one query.

The solution is to use a nested-set or nested-interval model. There are
several Rails plugins for this; awesome_nested_set is the one that I’ve
used most.

Thanks in advance.
Dave

Best

Marnen Laibow-Koser
http://www.marnen.org
[email protected]