Relations::Family 0.94

Relations::Family is a DBI/DBD::mysql Relational Query Engine module.

SYNOPSIS

# DBI, Relations::Family Script that creates some queries.

#!/usr/bin/perl

use DBI;
use Relations::Family;

$dsn = "DBI:mysql:finder";

$username = "root";
$password = ;

$dbh = DBI->connect($dsn,$username,$password,{PrintError => 1, RaiseError => 0});

$abstract = new Relations::Abstract($dbh);

$family = new Relations::Family($abstract);

$family->add_member(-name => account,
-label => Cust. Account,
-database => finder,
-table => account,
-id_field => acc_id,
-query => {-select => {id => acc_id,
label => "concat(cust_name, - ,balance)"},
-from => [account,customer],
-where => "customer.cust_id=account.cust_id",
-order_by => "cust_name"});

$family->add_member(-name => customer,
-label => Customer,
-database => finder,
-table => customer,
-id_field => cust_id,
-query => {-select => {id => cust_id,
label => cust_name},
-from => customer,
-order_by => "cust_name"});

$family->add_member(-name => purchase,
-label => Purchase,
-database => finder,
-table => purchase,
-id_field => pur_id,
-query => {-select => {id => pur_id,
label => "concat(
cust_name,
- ,
date_format(date, %M %D, %Y)
)"},
-from => [purchase,
customer],
-where => customer.cust_id=purchase.cust_id,
-order_by => [date desc,
cust_name]});

$family->add_lineage(-parent_name => customer,
-parent_field => cust_id,
-child_name => purchase,
-child_field => cust_id);

$family->add_rivalry(-brother_name => customer,
-brother_field => cust_id,
-sister_name => account,
-sister_field => cust_id);

$family->set_chosen(-label => Customer,
-ids => 2,4);

$available = $family->get_available(-label => Purchase);

print "Found $available->{count} Purchases:n";

foreach $id (@{$available->{ids_array}}) {

print "Id: $id Label: $available->{labels_hash}->{$id}n";

}

$family->add_value(-name => Cust. Account,
-sql => "concat(cust_name, - ,balance)",
-member_names => customer,account);

$family->add_value(-name => Paid,
-sql => "if(balance > 0,NO,YES)",
-member_names => account);

$family->add_value(-name => Customer,
-sql => cust_name,
-member_names => customer);

$family->add_value(-name => Purchase,
-sql => "concat(
cust_name,
- ,
date_format(date, %M %D, %Y)
)",
-member_names => purchase,customer);

$reunion = $family->get_reunion(-data => Paid,Purchase,
-use_labels => Customer,
-order_by => Customer,Purchase);

$matrix = $abstract->select_matrix(-query => $reunion);

print "Found " . scalar @$matrix . " Values:n";

foreach $row (@$matrix) {

print "Customer: $row->{Customer}n";
print "Purchase: $row->{Purchase}n";
print "Paid: $row->{Paid}nn";

}

$dbh->disconnect();