# Efficiently building collections of nested sets

**URL:** <https://www.ruby-forum.com/t/efficiently-building-collections-of-nested-sets/184762>\
**Category:** Rails\
**Created:** [March 8, 2010, 9:01am UTC](https://www.ruby-forum.com/t/efficiently-building-collections-of-nested-sets/184762 "2010-03-08T09:01:30Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![DGerton](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/d/7cd45c/32.png) [@DGerton](https://www.ruby-forum.com/u/DGerton)\
**Post date:** [March 8, 2010, 9:01am UTC](https://www.ruby-forum.com/t/efficiently-building-collections-of-nested-sets/184762/1 "2010-03-08T09:01:30Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![DGerton](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/d/7cd45c/32.png) [@DGerton](https://www.ruby-forum.com/u/DGerton)\
**Post date:** [March 9, 2010, 6:40pm UTC](https://www.ruby-forum.com/t/efficiently-building-collections-of-nested-sets/184762/2 "2010-03-09T18:40:31Z")

</div>

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](http://www.marnen.org)  
removed\_email\_address@domain.invalid