Forum: Ruby on Rails Efficiently building collections of nested sets

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

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
marnen@marnen.org