Efficiently building collections of nested sets

rubyonrails-talk
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