I have in my database a table similar to this one:
+----+--------+-----------------+
| id | parent | name |
+----+--------+-----------------+
| 1 | 0 | father 1 |
| 2 | 0 | father 2 |
| 3 | 1 | child 1 - 3 |
| 4 | 0 | father 4 |
| 5 | 2 | child 2 - 5 |
| 6 | 2 | child 2 - 6 |
| 7 | 1 | child 1 - 7 |
+----+--------+-----------------+
The list is purposedly only sorted by the primary key (id). The logic is simple, if the parent is 0, then it's a father category, otherwise is a child or child-child (and so on).
Using Ruby (Sinatra and DataMapper), I want to achieve this cascaded list:
[
{
"id":1,
"parent":0,
"name":"father 1",
"childs":[
{
"id":3,
"parent":1,
"name":"child 1 - 3",
"childs":[ ]
},
{
"id":7,
"parent":1,
"name":"child 1 - 7",
"childs":[ ]
}
]
},
{
"id":2,
"parent":0,
"name":"father 2",
"childs":[
{
"id":5,
"parent":1,
"name":"child 2 - 5",
"childs":[ ]
},
{
"id":7,
"parent":1,
"name":"child 2 - 6",
"childs":[ ]
}
]
},
{
"id":4,
"parent":0,
"name":"father 4",
"childs":[ ]
}
]
I've created the Category class as follows...
class Category
include DataMapper::Resource
property :id, Serial
property :name, String, :length => 2..50, :required => true
property :parent, Integer, :default => 0
# All categories may have sub-categories
has n, :childs, 'Category', :parent_key => :id, :child_key => :parent, :constraint => :protect
end
And I'm obtaining the parent list by asking for parents only to the database:
{ :data => Category.all(:parent => 0, :order => [ :name.asc ]), :total => Category.count(:parent=>0) }.to_json
But I can't manage to get the list of childs for each category.
I could create a recursive "each -> Category.all" query but that would end in a lot of queries for a table with just 100s rows (one per each parent, one per each child, etc) instead of just a couple ones. I could cache the list if there's not a better way to do this.
How can I automatically load and merge in cascade all the childs for each parent and each child (categories, may have sub-categories) without a lot of queries?
