Creating Dynamic Aarrays from SQL recordset

CPAN::Forum

Thread

DBIx-DataModel [search.cpan.org] [Kobes search] [View and report bugs] [Ratings 4.0

(1) ]

[AnnoCPAN] [CPAN Tests results] [CPANTS]

Posted on Sun Oct 29 21:34:10 2006 by kez

Creating Dynamic Aarrays from SQL recordset

Will the DBIx::DataModel module work for creating the following data structure with the data returned from a database query.

Data returned by query:

id name parent_id
1 Apparel NULL
2 Adult Jerseys 1
3 Girl-Cheer 1
4 Infant Cheer 1
5 Men's Polo 1
6 Men's 2
7 Women's 2
21 Auto NULL
22 Bumper Sticker 21
23 Car Flag 21
24 Car Magnet 21
57 Trees NULL

Arrray's Created Dynamically:

Parents
@menu ( "Apparel" , "Auto", "Trees" )

Children
@apparel ( "Adult Jerseys", "Girl-Cheer", "Men's Polo")
@auto ("Bumper Sticker", "Car Flag", "Car Magnet")

Children of Children
@adult_jerseys ("Men's", "Women's")

etc...

Kevin

Direct Responses: 3400 | Write a response

Posted on Tue Oct 31 22:29:31 2006 by dami in response to 3364

Re: Creating Dynamic Aarrays from SQL recordset

Your question is about a reflexive association (an association from a table to itself). Let's assume your reflexive table has SQL name "Data", with columns "id", "name" and "parent_id". On the Perl side we have to declare a schema and declare the table within that schema (let's name it "My::Data") :

DBIx::DataModel->Schema('Sch'); Sch->Table(qw/My::Data Data id/);

Next we declare the reflexive association, which creates methods for retrieving the parent or the children of any record.

Sch->Association([qw/My::Data parent 0..1 id /], [qw/My::Data children * parent_id/]);

Now we can connect the schema to the database and find the root items

my $dbh = DBI->connect(...); Sch->dbh($dbh); my $roots = My::Data->select(-where => {parent_id => undef});

Each root object has a "children" method to find its children, so it's easy to print the whole structure

foreach my $root (@$roots) { print_tree($root, ""); } sub print_tree { my ($root, $offset) = @_; print "$offset$root->{name}\n"; my $children = $root->children; foreach my $child (@$children) { print_tree($child, "$offset "); } }

Instead, we can tell the schema to auto-expand the association, and then directly build the tree for each root

use constant true => 1; My::Data->AutoExpand('children'); foreach my $root (@$roots) { $root->autoExpand(true); # recursive expansion print Dumper($root); # a structure with all descendants }

Write a response

Service launched on 2nd of February 2005.
Comments, and other submissions on CPAN Forum are Copyright 2005-2007, their respective owners.
Site maintainer is not responsible for content. Privacy Policy

Maintained and hosted by Gabor Szabo at Perl Training Israel