Could you please add a $DBIx::Recordset ::Debug =2 ; and send the SQL SELECT statement that DBIx::Recordset generates ?
Gerald ------------------------------------------------------------- Gerald Richter ecos electronic communication services gmbh Internetconnect * Webserver/-design/-datenbanken * Consulting Post: Tulpenstrasse 5 D-55276 Dienheim b. Mainz E-Mail: [EMAIL PROTECTED] Voice: +49 6133 925131 WWW: http://www.ecos.de Fax: +49 6133 925152 ------------------------------------------------------------- ----- Original Message ----- From: "Alexei V. Alexandrov" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Friday, November 16, 2001 12:24 PM Subject: DBIx::Recordset linked tables? > Hello everyone, > > I have a question regarding DBIx::Recordset and it's ability to work with > multiple tables. Here is my situation: > 1. I have two tables one contains statistical info about users who accessed > the shop and the other one about the profit. > 2. By doing some queries on these two tables i get the following data for > example: > > Query the orders table: > +-----------------+---------+ > | ID | PROFIT | > +-----------------+---------+ > | Nov | 3577.50 | > | Oct | 7141.65 | > | Sep | 338.00 | > +-----------------+---------+ > > Query the users table: > > +-------+-------------+ > | B_ID | VHITS | > +-------+-------------+ > | Nov | 149542 | > | Oct | 288659 | > | Sep | 5649 | > +-------+-------------+ > > 3. Then i set up DBIx::Recordset to link these two resultslike this > > *set = DBIx::Recordset->Search({ > '!DataSource' => $dbh, > '!Table' => 'A', > '!Fields' => 'DATE_FORMAT(LVISIT, ?) AS B_ID, > SUM(UTOTAL) AS VHITS', > '!NameField' => ['B_ID','VHITS'], > '!Links' => { > '-B' => { > '!Table' => 'B', > '!Fields' => 'DATE_FORMAT(LVISIT, ?) AS > ID, PROFIT', > '!NameField' => ['ID','PROFIT'], > '!LinkedField' => 'ID', > '!MainField' => 'B_ID', > '$where' => 'LVISIT >= ? AND LVISIT <= ? > AND LAST_AFFILATE LIKE ?', > '$values' => ['%b','2001-09-02', > '2001-11-16', '%1556-%'], > '$order' => 'ID' > } > }, > '$where' => 'LVISIT >= ? AND LVISIT <= ? AND > LAST_AFFILATE LIKE ?', > '$values' => ['%b','2001-09-02', '2001-11-16', > '%1556-%'], > '$group' => 'B_ID', > '$order' => 'B_ID' > }); > > my $sth = $set->StHdl(); > my $ptot = 0; > my $utot = 0; > my $c = 0; > > print join(" , ", @{$sth->{NAME}}), "\n"; > > while (my $rec = $set->Next) { > my $sum = 0; > while (my $b = ${$rec->{-B}}->Next) { > $sum += $b->{PROFIT}; > } > print $sum . " " . $rec->{B_ID} . " , " . $rec->{VHITS} . "\n"; > $ptot += $sum; > $utot += $set{VHITS}; > } > > print "Total: $ptot ($utot)\n"; > > ------------------------------------------- > And i get the following output: > B_ID , VHITS > 11057.15 Nov , 149572 > 11057.15 Oct , 288659 > 11057.15 Sep , 5649 > Total: 33171.45 (443880) > > So it means that foreach record in table A the link fetches all the records > in table B not comparing key/value pairs... > Where can i be wrong? The tables described above are temporary mysql tables, > create by using "create temporary a select ...". I even get this result the > relation is not 1:1 but 1:many. This is a test script i just wrote to > explain the situation. > Any help would be great. > > --- > Best regards, > Alexei V. Alexandrov > > > --------------------------------------------------------------------- > To unsubscribe, e-mail: [EMAIL PROTECTED] > For additional commands, e-mail: [EMAIL PROTECTED] > > --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
