mysql - (ruby) How can I retrieve a list of hashes cascaded by parent_id? -

mysql - (ruby) How can I retrieve a list of hashes cascaded by parent_id? -

i have in database table similar one:

+----+--------+-----------------+ | id | parent | name | +----+--------+-----------------+ | 1 | 0 | father 1 | | 2 | 0 | father 2 | | 3 | 1 | kid 1 - 3 | | 4 | 0 | father 4 | | 5 | 2 | kid 2 - 5 | | 6 | 2 | kid 2 - 6 | | 7 | 1 | kid 1 - 7 | +----+--------+-----------------+

the list purposedly sorted primary key (id). logic simple, if parent 0, it's father category, otherwise kid or child-child (and on).

using ruby (sinatra , datamapper), want accomplish 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 category class follows...

class category include datamapper::resource property :id, serial property :name, string, :length => 2..50, :required => true property :parent, integer, :default => 0 # categories may have sub-categories has n, :childs, 'category', :parent_key => :id, :child_key => :parent, :constraint => :protect end

and i'm obtaining parent list asking parents database:

{ :data => category.all(:parent => 0, :order => [ :name.asc ]), :total => category.count(:parent=>0) }.to_json

but can't manage list of childs each category.

i create recursive "each -> category.all" query end in lot of queries table 100s rows (one per each parent, 1 per each child, etc) instead of couple ones. cache list if there's not improve way this.

how can automatically load , merge in cascade childs each parent , each kid (categories, may have sub-categories) without lot of queries?

mysql sinatra associations parent-child ruby-datamapper