Index
- NAME
- SYNOPSIS
- ABSTRACT
- DESCRIPTION
- LIST OF RELATIONS::FAMILY FUNCTIONS
- LIST OF RELATIONS::FAMILY PROPERTIES
- RELATIONS::FAMILY DEMO - FINDER
- CHANGE LOG
- TODO LIST
- OTHER RELATED WORK
Code Index:
NAME
Relations::Family - 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'}\n\n";
}
$dbh->disconnect();
ABSTRACT
This perl module uses perl5 objects to simplify searching through and reporting on large, complex MySQL databases, especially those with foreign keys. It uses an object orientated interface, complete with functions to create and manipulate the relational family.
The current version of Relations::Family is available at
http://www.gaf3.com
DESCRIPTION
WHAT IT DOES
With Relations::Family you can create a 'family' of members for querying records. A member could be a table, or it could be a query on a table, like all the different months from a table's date field. Once the members are created, you can specify how those members are related, who's using who as a foreign key lookup, and what values in members you might be interested in reporting on, like whether a customer has paid their bill.
Once the 'family' is complete, you can select records from one member, and the query all the matching records from another member. For example, say you a product table being used as a lookup for a order items tables, and you want to find all the order items for a certain product. You can select that product's record from the product member, and then view the order item records to find all the order items for that product.
You can also build a large query for report purposes using the selections from various members as well as values you might be interested in. For example, say you want to know which customer are paid up and how much business they've generated in the past. You can specify which members' selections you want to use to narrow down the report and which values you'd like in the report and then use the query returned to see who's paid and for how much.
CALLING RELATIONS::FAMILY ROUTINES
Most standard Relations::Family routines use both an ordered, named and hashed argument calling style. (All except for to_text()) This is because some routines have as many as eight arguments, and the code is easier to understand given a named or hashed argument style, but since some people, however, prefer the ordered argument style because its smaller, I'm glad to do that too.
If you use the ordered argument calling style, such as
$family->add_lineage('customer','cust_id','purchase','cust_id');
the order matters, and you should consult the function defintions later in this document to determine the order to use.
If you use the named argument calling style, such as
$family->add_lineage(-parent_name => 'customer',
-parent_field => 'cust_id',
-child_name => 'purchase',
-child_field => 'cust_id');
the order does not matter, but the names, and minus signs preceeding them, do. You should consult the function defintions later in this document to determine the names to use.
In the named arugment style, each argument name is preceded by a dash. Neither case nor order matters in the argument list. -name, -Name, and -NAME are all acceptable. In fact, only the first argument needs to begin with a dash. If a dash is present in the first argument, Relations::Family assumes dashes for the subsequent ones.
If you use the hashed argument calling style, such as
$family->add_lineage({parent_name => 'customer',
parent_field => 'cust_id',
child_name => 'purchase',
child_field => 'cust_id'});
or
$family->add_lineage({-parent_name => 'customer',
-parent_field => 'cust_id',
-child_name => 'purchase',
-child_field => 'cust_id'});
the order does not matter, but the names, and curly braces do, (minus signs are optional). You should consult the function defintions later in this document to determine the names to use.
In the hashed arugment style, no dashes are needed, but they won't cause problems if you put them in. Neither case nor order matters in the argument list. parent_name, Parent_Name, PARENT_NAME are all acceptable. If a hash is the first argument, Relations::Family assumes that is the only argument that matters, and ignores any other arguments after the {}'s.
QUERY ARGUMENTS
Some of the Relations functions recognize an argument named query. This argument can either be a hash or a Relations::Query object.
The following calls are all equivalent for $object->function($query).
$object->function({select => 'nothing',
from => 'void'});
$object->function(Relations::Query->new(-select => 'nothing',
-from => 'void'));
LIST OF RELATIONS::FAMILY FUNCTIONS
An example of each function is provided in either 'test.pl' and 'demo.pl'.
new
$family = new Relations::Family($abstract); $family = new Relations::Family(-abstract => $abstract);
Creates creates a new Relations::Family object using a Relations::Abstract object.
add_member
$family->add_member($name,
$label,
$database,
$table,
$id_field,
$query,
$alias);
$family->add_member(-name => $name,
-label => $label,
-database => $database,
-table => $table,
-id_field => $id_field,
-query => $query,
-alias => $alias);
Creates and adds a member to a family. There's three basic groups of arguments in an add_member call. The first group sets how to name the member. The second sets how to configure the member. The third group explains how to create the query to display the member's records for selection.
$name and $label - In the first group, $name and $label set the internal and external identity, so both must be unique to the family. Typically, $name is a short string used for quickly specifying a member when coding with a family, while $label is a longer string used to display the identity of a member to user using the program. $label can have spaces within it, $name cannot.
$database, $table, $alias and $id_field - In the second group, $database, $table and $id_field set the MySQL properties. The $database and $table variables are the database and table used by the member, while $id_field is the member's table's primary key field. Relations::Family uses this info when connecting members to each other during a query.
Two or more members might use the same table in a database, or they might use the same table name in two different databases. Under either of these circumstances, if just the table name was used when building queries, MySQL would get confused. Enter $alias. This value is used to alias the table of the member. If no alias is sent, this value is set to the table name of the member.
$query - This is the query used to populate a member's selection list. The query must select two fields, 1) the id of the member, labeled 'id', and 2) the label of the member, labeled 'label'. The id field is what identifies one record from another in a way that is understandable to the database. The id field is usually the primary key. The label field is used to distinguish one record from another in a way that is understandable to the user. If a Relations::Query object is sent (see Query Arguments above), the object is cloned so the orginal is not modified.
add_lineage
$family->add_lineage($parent_name,
$parent_field,
$child_name,
$child_field);
$family->add_lineage(-parent_name => $parent_name,
-parent_field => $parent_field,
-child_name => $child_name,
-child_field => $child_field);
$family->add_lineage(-parent_label => $parent_label,
-parent_field => $parent_field,
-child_label => $child_label,
-child_field => $child_field);
Adds a one-to-many relationship to a family. This is used when a member, the child, is using another member, the parent, as a lookup.
$parent_name or $parent_label - Specifies the parent member by name or label.
$parent_field - Specifies the field in the parent member that holds the values used by the child member's child_field, usually the parent member's primary key.
$child_name or $child_label - Specifies the child member by name or label.
$child_field - Specifies the field in the child member that stores the values of the parent member's field.
add_rivalry
$family->add_rivalry($brother_name,
$brother_field,
$sister_name,
$sister_field);
$family->add_rivalry(-brother_name => $brother_name,
-brother_field => $brother_field,
-sister_name => $sister_name,
-sister_field => $sister_field);
$family->add_rivalry(-brother_label => $brother_label,
-brother_field => $brother_field,
-sister_label => $sister_label,
-sister_field => $sister_field);
Adds a one-to-one relationship to a family. This is used when a member, the sister, is using another member, the parent, as a lookup, and there is no more than one sister record for a given brother record.
$brother_name or $brother_label - Specifies the brother member by name or label.
$brother_field - Specifies the field in the brother member that holds the values used by the sister member's sister_field.
$sister_name or $sister_label - Specifies the sister member by name or label.
$sister_field - Specifies the field in the sister member that stores the values of the brother member's field.
add_value
$family->add_value($name,
$sql,
$member_names);
$family->add_value(-name => $name,
-sql => $sql,
-member_names => $member_names);
$family->add_value(-name => $name,
-sql => $sql,
-member_labels => $member_labels);
Adds a value to a family object. Values are used when creating a report query from a family object using the get_reunion function. Each value object is a column in the report query.
$name - The name of the column in the report query from get_reunion.
$sql - The sql code for a value. When the report query is created, all the values appear in the form "select $sql as $name". When referencing a member's table in the $sql of a value, make sure you use the alias from a member, if the alias is any different from the table name,
$member_names or $member_labels - Specifies the members needed, by name or label, by this value to build its $sql field. Either can be a comma delimitted string or array reference.
get_chosen
$chosen = $family->get_chosen($name); $chosen = $family->get_chosen(-name => $name); $chosen = $family->get_chosen(-label => $label);
Returns a member's selected records in a couple different forms, as well as the other goodies to control the selection process.
$name or $label - Specifies the member by name or label.
$chosen - A hash reference of all returned values.
$chosen->{count} - The number of selected records.
$chosen->{ids_string} - A comma delimtted string of the ids of the selected records.
$chosen->{ids_array} - An array reference of the ids of the selected records.
$chosen->{ids_select} - An array reference of the ids and labels separated by tabs: "$id\t$label" This is used to populate the <OPTION> values of an HTML <SELECT> list so that the list selections returned from the CGI module contain both the id and label of each selected record.
$chosen->{labels_string} - A tab delimtted string of the labels of the selected records. If labels were not set with set_chosen, this is not available.
$chosen->{labels_array} - An array reference of the labels of the selected records. If labels were not set with set_chosen, this is not available.
$chosen->{labels_hash} - A hash reference of the labels of the selected records, keyed by the selected ids. If labels were not set with set_chosen, this is not available.
$chosen->{labels_select} - A hash reference of the labels of the selected records, keyed by ids and labels separated by tabs: "$id\t$label". This is used to populate the <OPTION> display of an HTML <SELECT> list while using the CGI module. If labels were not set with set_chosen, this is not available.
$chosen->{match} - The match argument set with set_chosen().
$chosen->{group} - The group argument set with set_chosen().
$chosen->{filter} - The filter argument set with set_chosen().
$chosen->{limit} - The limit argument set with set_chosen().
$chosen->{ignore} - The ignore argument set with set_chosen().
set_chosen
$family->set_chosen($name,
$ids,
$labels,
$match,
$group,
$filter,
$limit,
$ignore);
$family->set_chosen(-name => $name,
-ids => $ids,
-labels => $labels,
-match => $match,
-group => $group,
-filter => $filter,
-limit => $limit,
-ignore => $ignore);
$family->set_chosen(-label => $label,
-ids => $ids,
-labels => $labels,
-match => $match,
-group => $group,
-filter => $filter,
-limit => $limit,
-ignore => $ignore);
$family->set_chosen(-label => $label,
-selects => $selects,
-match => $match,
-group => $group,
-filter => $filter,
-limit => $limit,
-ignore => $ignore);
Sets the member's records selected by a user, as well as some other goodies to control the selection process.
$name or $label - Specifies the member by name or label.
$ids - The ids selected. Can be a comma delimitted string, an array.
$labels - The labels selected. Can be a tab delimitted string, an array, or a hash keyed by $ids. It is isn't necessary to send these, unless you want the selected labels returned by get_chosen.
$selects - An array of selected ids and labels. Each array member is a string of the id and label value separated by a tab: "$id\t$label". This when you used the ids_select and labels_select from get_available() to populate a <SELECT> list using the CGI module.
$match - Match any or all. Null or 0 for any, 1 for all. This deals with multiple selections from a member and how that affects matching records from another member. If a member is set to match any, calling get_available() for another member will return records from the second member that are connected to any of the first member's selections. If a member is set to match all, calling get_available() on another member will return records from the second member that are connected to all of the first member's selections.
$group - Group include or exclude. Null or 0 for include, 1 for exclude. This deals with whether to return matching records or non matching records. If a member is set to group include, calling get_available() for another member will return records from the second member that are connected to the first member's selections. If a member is set to group exclude, calling get_available() on another member will return records from the second member that are not connected to the first member's selections.
$filter - Filter labels. In order to simplify the selection process, you can specify a filter to only show a select group of records from a member for selecting. The filter argument accepts a string, $filter, and places it in the clause "having label like '%$filter%'".
$limit - Limit returned records. In order to simplify the selection process, you can specify a limit clause to only show a certain number of records from a member for selecting. The limit argument accepts a string, $limit, and places it in the clause "limit $limit", so it can be a single number, or two numbers separated by a comma.
$ignore - Ignore or not. Null or 0 for don't ignore, 1 for ignore. If a member is set to don't ignore, calling get_available() for another member will return records from the second member that are related in some way (depending on match and group) to the first member's selections. If a member is set to ignore, calling get_available() on another member will return records from the second member while completely ignoring the first member's selections.
get_available
$available = $family->get_available($name); $available = $family->get_available(-name => $name); $available = $family->get_available(-label => $label);
Returns a member's available records, records related in some way to the currently selected records in other members, which are not being ignored.
$name or $label - Specifies the member by name or label.
$available - A hash reference of all returned values.
$available->{count} - The number of available records.
$available->{ids_array} - An array reference of the ids of the available records.
$available->{ids_select} - An array reference of ids and labels. Each array member is a record's id and label separated by a tab: "$id\t$label". This is used to populate a <SELECT> list using the CGI module so that you can see both the ids and labels selected by a user.
$available->{labels_array} - An array reference of the labels of the available records.
$available->{labels_hash} - A hash reference of the labels of the available records, keyed by the available ids.
$available->{labels_select} - A hash reference of the labels of the available records, keyed by a record's id and label separated by a tab: "$id\t$label". This is used to populate a <SELECT> list using the CGI module so that you can see both the ids and labels selected by a user.
choose_available
$chosen = $family->choose_available($name); $chosen = $family->choose_available(-name => $name); $chosen = $family->choose_available(-label => $label);
Narrows down a member's chosen records using the available records to that member. So if five records are selected in a member, but only three of those records are now available (as if called with get_available()), this function will cause the member to only have those three records chosen.
$name or $label - Specifies the member by name or label.
$chosen - A hash reference of all returned values. See the get_chosen() function for all values within the hash.
get_reunion
$reunion = $family->get_reunion($data,
$use_names,
$group_by,
$order_by);
$reunion = $family->get_reunion(-data => $data,
-use_names => $use_names,
-group_by => $group_by,
-order_by => $order_by);
$reunion = $family->get_reunion(-data => $data,
-use_labels => $use_labels,
-group_by => $group_by,
-order_by => $order_by);
$reunion = $family->get_reunion(-data => $data,
-use_name_ids => $use_name_ids,
-group_by => $group_by,
-order_by => $order_by);
$reunion = $family->get_reunion(-data => $data,
-use_label_ids => $use_label_ids,
-group_by => $group_by,
-order_by => $order_by);
Returns a report query of the values specified by $data, grouped and ordered by the values specified by $group_by and $order_by, using the chosen ids of members specified by $use_names or $use_labels, or the ids specified by $use_name_ids or $use_label_ids.
$data - Specifies the values by name to be selected in the reunion.
$use_names or $use_labels - Specifies by name or label which members' chosen ids to use in narrowing down the report query. Either can be a comma delimitted string or array reference.
$use_name_ids or $use_label_ids - Specifies by name or label which ids to use in narrowing down the report query. Must be a hash ref of strings of comma delimitted id values, keyed by name or label.
$group_by and $order_by - Specifies the values by name to use in the group by and order by clause of the report query. Either can be a comma delimitted string or array reference.
$reunion - The report query in the form of a Relations::Query object.
to_text
$text = $family->to_text($string,$current);
Returns a text representation of a family. Useful for debugging purposes.
$string - String to use for indenting.
$current - Current number of indents.
$text - Textual representation of the family object.
LIST OF RELATIONS::FAMILY PROPERTIES
abstract
The Relations::Abstract object a family uses to query and such.
members
An array reference of the members in a family.
names
A hash reference of the members in a family, keyed by members' names.
labels
A hash reference of the members in a family, keyed by members' labels.
values
A hash reference of the values in a family, keyed by values' names.
RELATIONS::FAMILY DEMO - FINDER
Setup
Included with this distribution is demo.pl, which demonstrates all the listed functionality of Relations::Family. You must have MySQL, Perl, DBI, DBD-MySQL, Relations, Relations::Query, Relations::Abstract, and Relations::Family installed.
After installing everything, run demo.pl by typing
perl demo.pl
while in the Relations-Family installation directory.
Overview
This demo revolves around the finder database. This database is for a made up company that sells three different types of products - Toiletry: Soap, Towels, etc., Dining: Plates Cups, etc. and Office: Phones, Faxes, etc. The demo is an app that allows you to search through that database
Structure
|---------|
| item | |------------|
|---------| | product | |-----------|
| item_id | |------------| | type |
/-M-| pur_id | /-1-| prod_id | |-----------|
| | prod_id |-M-/ | prod_name | /-1-| type_id |
| | qty | | type_id |-M-/ | type_name |
| |---------| |------------| |-----------|
|
| |---------| |--------------|
| | pur_sp | | sales_person |
| |---------| |--------------|
| | ps_id | /-1-| sp_id | |----------|
|-M-| pur_id | / | f_name | | region |
|-----------| | | sp_id |-M-/ | l_name | |----------|
| purchase | | |---------| | reg_id |-M--1-| reg_id |
|-----------| | |--------------| | reg_name |
| pur_id |-1-/ |----------|
| cust_id |-M-\ |--------------|
| date | \ | customer | |--------------|
|-----------| \ |--------------| | account |
\-1-| cust_id |-1--\ |--------------|
| cust_name | \ | acc_id |
| phone | \-1-| cust_id |
|--------------| | balance |
|--------------|
There's a type table for the different types of products, and a product table for the different products. There's also a one-to-many relationship between type to product, because each product is of a specific type.
A similar relationship exists between the sales_person table, which holds all the different sales people, and the region table, which holds the regions for the sales peoples. Each sales person belongs to a particular region, so there's a one-to-many relationship from the region table to the sales_person table.
If there's sellers, there's buyers. This is the function of the customer table. There is also an account table, for the accounts for each customer. Since each customer has only one account, there is merely a one-to-one relationship between customer and account.
With sellers and buyers, there must be purchases. Enter the purchase table, which holds all the purchases. Since only one customer makes a certain purchase, but one customer could make many purchases, there is a one-to-many relationship from the customer table to the purchase table.
Each purchase contains some number of products at various quantities. This is the role of the item table. One purchase can have multiple items, so there is a one-to-many relationship from the purchase table to the item table.
A product is the item purchased at different quantities, and a product can be in multiple purchases. Thus, there is a one-to-many relationship from the product table to the item table.
Finally, zero or more sales people can get credit for a purchase, so there is many-to-many relationship between the sales_person and purchase tables. This relationship is handled by the pur_sp table, so there is a one-to-many relationship from the purchase table to the pur_sp table and a one-to-many relationship from the sales_person table to the pur_sp table.
Role of Family
Family's role in this is true to it's name sake: It brings all of this into one place, and allows tables to connect to one another. A member in the finder family is created for each table in the finder database, and a lineage (for one-to-many's) or a rivalry (for one-to-one's) for each relationship.
With Family, you can select records from one member and find all the connecting records in other members. For example, to see all the products made by a purchase, you'd go to the purchase member, and select the purchase in question, and then go to the product's member. The avaiable records in product would be all the product on that purchase.
Usage
To run the demo, make sure you've followed the setup instructions, and go to the directory in which you've placed demo.pl and finder.pm. Run demo.pl like a regular perl script.
The demo starts with a numbered listing of all the members of the finder family. To view available records from a member and/or make selections, type in the member's number and hit return.
The first thing you'll be asked is if you want to choose available. This narrows down the current selected members of a list by the available records for a list. Enter 'Y' for yes, and 'N' for no. It defaults to 'N' so a blank is the same as no.
You'll then get two questions regarding the presentation of a member's records. I'll go into both here.
Limit is for displaying only a certain number of avaiable records at a time. It's fed into a MySQL limit clause so it can be one number, or two separated by a comma. To just see X number of rows from the begining, just enter X. To see X number of rows starting at Y, enter Y,X.
Filter is for filtering available records for display. It takes a string, and only returns member's available records that have the entered string in their label. Just enter the text to filter by.
You'll then get a numbered listing of all the available records for that member, as well as the match, group, ignore, limit and filter settings for that member.
Next, you'll get some questions regarding which records are to be selectecd, and how those selections are to be used (or not used!). I'll go into them here.
Selections are the records you want to choose. To choose records, type each number in, separating with commas.
Match is whether you want other lists to match any of your multiple selections from this member or all of them. 0 for many, 1 for all.
Group is whether you want to include what was selected in this member, or exclude was selected, in matching other member's records. 0 for include, 1 for exclude.
Finally, you'll be asked if you want to do this again. 'Y' for yes, 'N' for no. It defaults to 'Y', so just type return for yes. If you choose yes, you'll get a list of members, go through the selection/viewing process again.
If you press 'N', demo.pl will ask if you want to create a reunion. A reunion is like a final report query. It defaults to 'N', so just type return for no. If you choose no, the program will exit.
If you choose yes, you'll get a list of all the values in finder. At the Data prompt, type in all values (by number) you'd like to see in the report query, separating each with a comma. At the Group By prompt, type in all values (by number) you'd like to group by in the report query, separating each with a comma. At the Order By prompt, well, I bet you can figure it out.
After filling out the value information, a list of all members will be displayed. You'll be asked which members chosen values should be used in the query. Enter the numbers of the members to use for this.
After that's all set, you'll get the customized results of your report query.
Examples
All together, this system can be used to figure out a bunch of stuff. Here's some ways to query certain records. With each example, it's best to restart demo.pl for scratch (exit and rerun).
Limit and Filter - There are 17 Sales Persons in the database. Though this isn't terribly many, you can lower the number sales people displayed at one time with Family two different ways, by limitting or by filtering. Here's examples for both.
First, let's look at all the sales people - From the members list, select 7 for Sales Person. - Don't choose available, no limit, and no filter. (or just hit return) - There should be 17 available records:
(2) Mimi Butterfield (12) Jennie Dryden (6) Dave Gropenhiemer (14) Karen Harner (1) John Lockland (4) Frank Macena (13) Mike Nicerby (5) Joyce Parkhurst (17) Calvin Peterson (8) Fred Pirozzi (16) Mya Protaste (9) Sally Rogers (15) Jose Salina (3) Sheryl Saunders (11) Ravi Svenka (10) Jane Wadsworth (7) Hank Wishings
These are all the sales people. - No Selections (or just hit return) - Match = 0, Group = 0 (or just hit return) - Reply Y, to 'Again?' (to select another member)
Now, let's just look at the first 5 sales peoeple. - From the members list, select 7 for Sales Person. - Don't choose available. (or just hit return) - Set limit to 5. - No filter. (or just hit return) - There should be 5 available records:
(2) Mimi Butterfield (12) Jennie Dryden (6) Dave Gropenhiemer (14) Karen Harner (1) John Lockland
These are the first 5 sales people - No Selections (or just hit return) - Match = 0, Group = 0 (or just hit return) - Reply Y, to 'Again?' (to select another member)
How 'bout the last 5 sales people. - From the members list, select 7 for Sales Person. - Don't choose available. (or just hit return) - Set limit to 12,5. - No filter. (or just hit return) - There should be 5 available records:
(15) Jose Salina (3) Sheryl Saunders (11) Ravi Svenka (10) Jane Wadsworth (7) Hank Wishings
These are the last 5 sales people. Limit started at the 12th record, and allowed the next 5 records. - No Selections (or just hit return) - Match = 0, Group = 0 (or just hit return) - Reply Y, to 'Again?' (to select another member)
Finaly, let's find all the sales people that have the letter 'y' in the first or last name. - From the members list, select 7 for Sales Person. - Don't choose available, and no limit. (or just hit return) - Set filter to y. - There should be 6 available records:
(12) Jennie Dryden (13) Mike Nicerby (5) Joyce Parkhurst (16) Mya Protaste (9) Sally Rogers (3) Sheryl Saunders
These are all the people with the letter 'y' in their first or last name. - No Selections (or just hit return) - Match = 0, Group = 0 (or just hit return) - Reply N, to 'Again?' (to go to reunion) - Reply N, to 'Create Reunion?' (to quit)
The Selections Effect - A purchase contains one or more products, and you can see which product were purchased on a purchased order by selected a record from the purchase member, and viewing the avaiable records of the product member. Varney solutions made a purchase on jan 4th, 2001, and we'd like to see what they bought.
First, let's see all the products. - From the members list, select 3 for Product. - Don't choose available, no limit, and no filter. (or just hit return) - There should be 13 available records:
(6) Answer Machine (13) Bowls (9) Copy Machine (12) Cups (10) Dishes (8) Fax (7) Phone (11) Silverware (4) Soap (3) Soap Dispenser (5) Toilet Paper (1) Towel Dispenser (2) Towels
These are all the products. - No Selections (or just hit return) - Match = 0, Group = 0 (or just hit return) - Reply Y, to 'Again?' (to select another member)
Let's pick a purchase to view the products from. - From the members list, select 5 for Purchase. - Don't choose available, no limit, and no filter. (or just hit return) - There should be 8 available records:
(6) Last Night Diner - May 9th, 2001 (3) Harry's Garage - April 21st, 2001 (7) Teskaday Print Shop - April 7th, 2001 (4) Simply Flowers - March 10th, 2001 (2) Harry's Garage - February 8th, 2001 (8) Varney Solutions - January 4th, 2001 (1) Harry's Garage - December 7th, 2000 (5) Last Night Diner - November 3rd, 2000
- From the available records, select 8 for Varney Solutions' Purchase. - Match = 0, Group = 0 (or just hit return) - Reply Y, to 'Again?' (to select another member)
Now, we'll check out all the products on that purchase. - From the members list, select 3 for Product. - Don't choose available, no limit, and no filter. (or just hit return) - There should be 4 available records:
(6) Answer Machine (9) Copy Machine (8) Fax (7) Phone
These are the products purchased by Varney in January. - No Selections (or just hit return) - Match = 0, Group = 0 (or just hit return) - Reply N, to 'Again?' (to go to reunion) - Reply N, to 'Create Reunion?' (to quit)
Matching Multiple - You can also lookup purchases by products. Furthermore you can look purcahses up by selecting many products, and finding purchases that have any of the selected products. You can even find purchases that contain all the selected products.
First, let's see all the purchases. - From the members list, select 5 for Purchase. - Don't choose available, no limit, and no filter. (or just hit return) - There should be 8 available records:
(6) Last Night Diner - May 9th, 2001 (3) Harry's Garage - April 21st, 2001 (7) Teskaday Print Shop - April 7th, 2001 (4) Simply Flowers - March 10th, 2001 (2) Harry's Garage - February 8th, 2001 (8) Varney Solutions - January 4th, 2001 (1) Harry's Garage - December 7th, 2000 (5) Last Night Diner - November 3rd, 2000
- No Selections (or just hit return) - Match = 0, Group = 0 (or just hit return) - Reply Y, to 'Again?' (to select another member)
Now, we'll check out all the products, select a few, and set matching to any so we can purchases that have any (Soap or Soap Dispenser). - From the members list, select 3 for Product. - Don't choose available, no limit, and no filter. (or just hit return) - There should be 13 available records:
(6) Answer Machine (13) Bowls (9) Copy Machine (12) Cups (10) Dishes (8) Fax (7) Phone (11) Silverware (4) Soap (3) Soap Dispenser (5) Toilet Paper (1) Towel Dispenser (2) Towels
These are all the products. - From the available records, select 4,3 for Soap, and Soap Dispenser - Match = 0, Group = 0 (or just hit return) - Reply Y, to 'Again?' (to select another member)
Now, we'll see which purchases contain either Soap or Soap Dispenser. - From the members list, select 5 for Purchase. - Don't choose available, no limit, and no filter. (or just hit return) - There should be 3 available records:
(3) Harry's Garage - April 21st, 2001 (2) Harry's Garage - February 8th, 2001 (1) Harry's Garage - December 7th, 2000
- No Selections (or just hit return) - Match = 0, Group = 0 (or just hit return) - Reply Y, to 'Again?' (to select another member)
Now, we'll check out all the products, select a few, and set matching to all so we can purchases that have all (Soap and Soap Dispenser). - From the members list, select 3 for Product. - Don't choose available, no limit, and no filter. (or just hit return) - There should be 13 available records, Soap and Soap Dispenser *'ed,
(6) Answer Machine (13) Bowls (9) Copy Machine (12) Cups (10) Dishes (8) Fax (7) Phone (11) Silverware * (4) Soap * (3) Soap Dispenser (5) Toilet Paper (1) Towel Dispenser (2) Towels
These are all the products, with Soap and Soap Dispenser already selected. - From the available records, select 4,3 for Soap, and Soap Dispenser - Match = 1 (means 'all') - Group = 0 (or just hit return) - Reply Y, to 'Again?' (to select another member)
Now, we'll see which purchase contain both Soap and Soap Dispenser. - From the members list, select 5 for Purchase. - Don't choose available, no limit, and no filter. (or just hit return) - There should be 1 available record:
(1) Harry's Garage - December 7th, 2000
This is the only purchase that contains both Soap and Soap Dispenser. - No Selections (or just hit return) - Match = 0, Group = 0 (or just hit return) - Reply N, to 'Again?' (to go to reunion) - Reply N, to 'Create Reunion?' (to quit)
Group Inclusion/Exclusion - Sometimes you'd like to find all records the records not connected to your selections from a particular member. Say you wanted to check up on all the orders from customers, except the Harry's Garage, who would have already let you know if there was a problem.
First, let's see all the purchases. - From the members list, select 5 for Purchase. - Don't choose available, no limit, and no filter. (or just hit return) - There should be 8 available records:
(6) Last Night Diner - May 9th, 2001 (3) Harry's Garage - April 21st, 2001 (7) Teskaday Print Shop - April 7th, 2001 (4) Simply Flowers - March 10th, 2001 (2) Harry's Garage - February 8th, 2001 (8) Varney Solutions - January 4th, 2001 (1) Harry's Garage - December 7th, 2000 (5) Last Night Diner - November 3rd, 2000
- No Selections (or just hit return) - Match = 0, Group = 0 (or just hit return) - Reply Y, to 'Again?' (to select another member)
Let's pick a customer to not view the purchases from. - From the members list, select 1 for Customer. - Don't choose available, no limit, and no filter. (or just hit return) - There should be 5 available records:
(1) Harry's Garage (4) Last Night Diner (3) Simply Flowers (5) Teskaday Print Shop (2) Varney Solutions
- From the available records, select 1 for Harry's Garage. - Match = 0 (or just hit return) - Group = 1 (means 'exclude') - Reply Y, to 'Again?' (to select another member)
Now, we'll check out all the purchases not from Harry's Garage. - From the members list, select 5 for Purchase. - Don't choose available, no limit, and no filter. (or just hit return) - There should be 5 available records:
(6) Last Night Diner - May 9th, 2001 (7) Teskaday Print Shop - April 7th, 2001 (4) Simply Flowers - March 10th, 2001 (8) Varney Solutions - January 4th, 2001 (5) Last Night Diner - November 3rd, 2000
- No Selections (or just hit return) - Match = 0, Group = 0 (or just hit return) - Reply N, to 'Again?' (to go to reunion) - Reply N, to 'Create Reunion?' (to quit)
Basic Reunion - Let's first check out which customers owe us money, except for Varney Solutions of course, since we owe them a favor.
First, let's see all the customers. - From the members list, select 1 for Customer. - Don't choose available, no limit, and no filter. (or just hit return) - There should be 8 available records:
(1) Harry's Garage (4) Last Night Diner (3) Simply Flowers (5) Teskaday Print Shop (2) Varney Solutions
- From the available records, select 2 for Varney Solutions - Match = 0 (or just hit return) - Group = 1 (means 'exclude') - Reply N, to 'Again?' (to go to reunion) - Reply Y, to 'Create Reunion?' (to create a report)
Next, we have to decide which values we want in out report. After selecting create reunion, you should get a list of all values. - Data = select 0,1,2 for Customer, their Account and whether or not they've paid, Paid. - Group By = (just hit return) - Order By = 1, to order by customer.
Finally, we have to decide which members we're going to use to narrow down the query. A list will be displayed of all members in finder. - From the members list, select 1 for Customer. - You should get 4 results, each with 3 fields:
Customer: Harry's Garage Cust. Account: Harry's Garage - 134.87 Paid: NO Customer: Last Night Diner Cust. Account: Last Night Diner - 54.65 Paid: NO Customer: Simply Flowers Cust. Account: Simply Flowers - 0.00 Paid: YES Customer: Teskaday Print Shop Cust. Account: Teskaday Print Shop - 357.72 Paid: NO
Advanced Reunion - Let's see how many each each Office product we sold and sort from least to most of numbers sold.
First, let's see all the product types. - From the members list, select 8 for Type. - Don't choose available, no limit, and no filter. (or just hit return) - There should be 8 available records:
(3) Dining (2) Office (1) Toiletry
- From the available records, select 2 for Office - Match = 0 (or just hit return) - Group = 0 (or just hit return) - Reply N, to 'Again?' (to go to reunion) - Reply Y, to 'Create Reunion?' (to create a report)
Next, we have to decide which values we want in out report. - Data = select 3,9 for Product and Sold - Group By = select 3 for Product - Order By = select 9 for Sold
Finally, we have to decide which members we're going to use to narrow down the query. A list will be displayed of all members in finder. - From the members list, type 8 for Type. - You should get 4 results, each with 2 fields:
Sold: 2 Product: Answer Machine Sold: 2 Product: Fax Sold: 7 Product: Phone Sold: 15 Product: Copy Machine
CHANGE LOG
Relations-Family-0.94
Setting Get Reunion IDs
Added functionality to get_reunion to accept an alternative set of member' ids to use in the reunion query. This was done for the Relations::Report module's Iteration module.
Relations-Family-0.93
Add Member Query Cloning
If a Relations::Query object is sent to the add_member() function through $query, that query is now cloned so Relations-Family won't muck with the original.
Query Argument Functionality
Originally, the help files said that any function requiring a $query argument could take a Relations::Query argument, a hash, or a string. This isn't true (even before the above changes). The functions require a hash of query pieces (keyed with select, from, etc.) or a Relations::Query object. This is because Relations::Family builds on the query and needs the pieces separated to do this.
TODO LIST
Local Listing
Add functionality to the Member module so that a member can function as lookup values for a certain field. This would allow enum fields to be tied to a member, and allow user to select enum field values to narrow down a query.
Names and Labels of Values
Add a name and label property to the Value modules. This is so Family will be more XML compatible since the Value module's current name property can contain spaces and would be ill suited for an ID value since it can contain spaces. Value will then be like Member will a lowercase no spaces name for internals and a anything goes label for display purposes.
XML Functionality
Add functionality so that a Family module (and all its kids) can import and export their configuration to XML. This will be useful when the PHP and Java versions of Relations::Family come about. People will be able to port from one language to another with little effort.
OTHER RELATED WORK
Relations (Perl)
Contains functions for dealing with databases. It's mainly used as the foundation for the other Relations modules. It may be useful for people that deal with databases as well.
Relations-Query (Perl)
An object oriented form of a SQL select query. Takes hashes. arrays, or strings for different clauses (select,where,limit) and creates a string for each clause. Also allows users to add to existing clauses. Returns a string which can then be sent to a database.
Relations-Abstract (Perl)
Meant to save development time and code space. It takes the most common (in my experience) collection of calls to a MySQL database, and changes them to one liner calls to an object.
Relations-Admin (PHP)
Some generalized objects for creating Web interfaces to relational databases. Allows users to insert, select, update, and delete records from different tables. It has functionality to use tables as lookup values for records in other tables.
Relations-Family (Perl)
Query engine for relational databases. It queries members from any table in a relational database using members selected from any other tables in the relational database. This is especially useful with complex databases: databases with many tables and many connections between tables.
Relations-Display (Perl)
Module creating graphs from database queries. It takes in a query through a Relations-Query object, along with information pertaining to which field values from the query results are to be used in creating the graph title, x axis label and titles, legend label (not used on the graph) and titles, and y axis data. Returns a graph and/or table built from from the query.
Relations-Report (Perl)
A Web interface for Relations-Family, Reations-Query, and Relations-Display. It creates complex (too complex?) web pages for selecting from the different tables in a Relations-Family object. It also has controls for specifying the grouping and ordering of data with a Relations-Query object, which is also based on selections in the Relations-Family object. That Relations-Query can then be passed to a Relations-Display object, and a graph and/or table will be displayed.
Relations-Structure (XML)
An XML standard for Relations configuration data. With future goals being implmentations of Relations in different languages (current targets are Perl, PHP and Java), there should be some way of sharing configuration data so that one can switch application languages seamlessly. That's the goal of Relations-Structure A standard so that Relations objects can export/import their configuration through XML.