perl-embperl mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Terrence Brannon" <tb...@directsynergy.com>
Subject DBIx::Recordset - working with multiple tables
Date Wed, 27 Aug 2003 23:43:34 GMT
I have the following tables:

mysql> describe authors; describe titles; describe titleauthors;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| au_id    | varchar(11) |      | PRI |         |       |
| au_lname | varchar(40) |      | MUL |         |       |
| au_fname | varchar(20) |      |     |         |       |
| phone    | varchar(12) | YES  |     | NULL    |       |
| address  | varchar(40) | YES  |     | NULL    |       |
| city     | varchar(20) | YES  |     | NULL    |       |
| state    | char(2)     | YES  |     | NULL    |       |
| zip      | varchar(5)  | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
8 rows in set (0.00 sec)

+-----------+---------------+------+-----+---------+-------+
| Field     | Type          | Null | Key | Default | Extra |
+-----------+---------------+------+-----+---------+-------+
| title_id  | varchar(6)    |      | PRI |         |       |
| title     | varchar(80)   |      | MUL |         |       |
| type      | varchar(12)   | YES  |     | NULL    |       |
| pub_id    | varchar(4)    | YES  |     | NULL    |       |
| price     | decimal(8,2)  | YES  |     | NULL    |       |
| advance   | decimal(10,2) | YES  |     | NULL    |       |
| ytd_sales | int(11)       | YES  |     | NULL    |       |
| contract  | tinyint(1)    |      |     | 0       |       |
| notes     | varchar(200)  | YES  |     | NULL    |       |
| pubdate   | date          | YES  |     | NULL    |       |
+-----------+---------------+------+-----+---------+-------+
10 rows in set (0.01 sec)

+--------------+--------------+------+-----+---------+-------+
| Field        | Type         | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+-------+
| au_id        | char(11)     |      | PRI |         |       |
| title_id     | char(6)      |      | PRI |         |       |
| au_ord       | tinyint(4)   | YES  |     | NULL    |       |
| royaltyshare | decimal(5,2) | YES  |     | NULL    |       |
+--------------+--------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql>


... authors and titles both have a 1:n relation to titleauthors

And I wrote the following Recordset code to INNER JOIN them all:

require 'dbconn.pl';
use DBIx::Recordset;
use strict;
use vars qw(*set *set2 *set3);

{

    my %DEBUG = ('!Debug' => 0);

    *set = DBIx::Recordset -> Search
      ({
 conn_dbh(),  # returns ('!DataSource' => $dbh )
 %DEBUG,
 '!Table'    => 'authors'
       }) ;

    while ( my $rec = $set->Next) {
 print join "\t", $set{au_fname}, $set{au_lname}, $set{au_id}, $/;
 *set2 = DBIx::Recordset -> Search
   ({
     conn_dbh(),
     %DEBUG,
     '!Table'    => 'titleauthors',
     au_id              => $set{au_id}
    }) ;

 while ( my $rec2 = $set2->Next) {
     # warn 1.3;
     print "\t", $set2{title_id}, $/;

     # warn 1.4;
     *set3 = DBIx::Recordset -> Search
       ({
  conn_dbh(),
  %DEBUG,
  '!Table'    => 'titles',
  title_id       => $set2{title_id}
        });

     while ( my $rec3 = $set3->Next) {
  print "\t\t", $set3{title}, $/;

     }
 }
    }


}



I know there is a better way using !Link, but I am having problems figuring
out
how to do it. Here is my attempt, but it creates an error.

require 'dbconn.pl';
use DBIx::Recordset;
use strict;
use vars qw(*set *set2 *set3);

my %DEBUG = ('!Debug' => 0);

*set = DBIx::Recordset -> Search
  ({
    conn_dbh(),
    %DEBUG,
    '!Table'    => 'authors',
    '!Links'           => {

      '-titleauthors' => {

            '!Table' => 'titleauthors',
            '!LinkedField' => 'titleauthors.au_id',
            '!MainField' => 'authors.au_id'

           }
     }
   });


while ( my $rec = $set->Next) {

    warn $rec->{au_fname};
    my $row_count;
    while ( my $titleauthors = $set{'-titleauthors'}->Next ) {  ### FAILING
LINE
 warn $row_count++;
    }
}

=head1 ERROR:

Can't call method "Next" without a package or object reference at
3-table-join-link.pl line 30.
DB:  Disconnect (id=2, numOpen = 0)
[tbone@horse1 scripts]$


=cut




---------------------------------------------------------------------
To unsubscribe, e-mail: embperl-unsubscribe@perl.apache.org
For additional commands, e-mail: embperl-help@perl.apache.org


Mime
View raw message