Until now, we only have worked with one table. In real life, you often have to
deal with mulitple tables. For this reason, DBIx::Recordset helps you to
reduce the expense associated with dealing with multiple tables. The simplest
way to do this is to use the
parameters !TabJoin and !TabRelation to tell DBIx::Recordset to create
an SQL-join between two or more tables. This will link the tables together and
the result looks just like one great table. More interesting is the possibility to create "links". As an example, we'll take the same
table we used above and divide it into two tables: one table for the names and one
table for the towns. As a link we add an id-field. If the fields are following some naming
convention, DBIx::Recordset is able to find this link automatically. If fields are
named in another way, you have to tell DBIx::Recordset manually how the tables belong
together. Table name: firstname, name, town_id
Table town: id, town Here, every name has exactly one town and every town has a number of names assigned.
With a simple modification of our first example, we could get the same result
as above (except that we are now dealing with two tables instead of one):
[-
use DBIx::Recordset ;
$db = DBIx::Database -> new ('dbi:mysql:test') ;
$db -> TableAttr ('town', '!NameField', 'town') ;
$fdat{'!DataSource'} = $db ;
$fdat{'!LinkName'} = 3 ;
*set = DBIx::Recordset -> Search(\%fdat) ;
$names = $set -> Names ;
-]And the request would be: http://www.domain.com/path/to/example2.htm?!Table=name
The new thing here is the DBIx::Database object. It gathers meta-information
about the database and stores it for later use. Because of the names of the fields
the object can detect that the field town_id in the table name points to
field id in the table town. Additionally, we tell the DBIx::Database object
which column(s) contain the human-readable name of the table town.
These initialisations only have to be executed once. If you use
mod_perl, for example, you should be able to move these lines into
a common startup file. Also new is the parameter !LinkName. It tells DBIx::Recordset to return
the human-readable name (in our example, town) instead of the field which links
the two tables together (town_id in our example). Internally, DBIx::Recordset
generates an SQL-join, so there is only one SELECT command necessary and the result
is just the same as in the last example.
But what to do if we have the id of a town and want to display all the names that
belongs to it? An Embperl-page that does this job might look something like this: <body>
[-
use DBIx::Recordset ;
$fdat{'!DataSource'} = 'dbi:mysql:test' ;
$fdat{'!Table'} = 'town' ;
*set = DBIx::Recordset -> Search(\%fdat) ;
-] town: [+ $set{town} +]<br>
<table>
<tr>
<th>name</th><th>firstname</th>
</tr>
<tr>
[- $rec = $set{-name}[$row] -]
<td>[+ $rec -> {name} +]</td><td>[+ $rec -> {firstname} +]</td>
</tr>
</table>
</body>A request to that page might look like this: http://www.domain.com/path/to/example3.htm?id=5 In this example, we specify the name of the table directly inside the page, so it
can't be overwritten from outside. The call to Search returns the town for the
given query parameters. In our example, it will select the town with the id 5.
The command [+ $set{town} +] shows the value of the field town in the current
record. After the call to Search, this is the first selected record.
Next, we need to display all the names. This is very easy using the special field
-name. -name contains a sub-object for the table name. The query parameters
for this sub-object are set by DBIx::Recordset in such a way that it contains all
names which meet the link-condition. We just wrap it in a table and we are already done.
|
