> Could you please add a $DBIx::Recordset ::Debug =2 ; and send the SQL
SELECT
> statement that DBIx::Recordset generates ?
Yes, here it is:
DB: Use already open dbh for dbname (id=1, numOpen = 0)
DB: 'SELECT DATE_FORMAT(LVISIT, ?) AS B_ID, SUM(UTOTAL) AS VHITS FROM A
WHERE LVISIT >= ? AND LVISIT <= ? AND LAST_AFFILATE LIKE ?GROUP BY B_ID
ORDER BY B_ID ' bind_values=<%b 2001-09-02 2001-11-16 %1556-%>
bind_types=<>
DB: Use already open dbh for dbname (id=2, numOpen = 0)
DB: 'SELECT DATE_FORMAT(LVISIT, ?) AS ID, PROFIT FROM B WHERE LVISIT >= ?
AND LVISIT <= ? AND LAST_AFFILATE LIKE ?
GROUP BY ID ORDER BY ID ' bind_values=<%b 2001-09-02 2001-11-16 %1556-%>
bind_types=<>
DB: Use already open dbh for dbname (id=3, numOpen = 0)
DB: 'SELECT DATE_FORMAT(LVISIT, ?) AS ID, PROFIT FROM B WHERE LVISIT >= ?
AND LVISIT <= ? AND LAST_AFFILATE LIKE ?
GROUP BY ID ORDER BY ID ' bind_values=<%b 2001-09-02 2001-11-16 %1556-%>
bind_types=<>
DB: Use already open dbh for dbname (id=4, numOpen = 0)
DB: 'SELECT DATE_FORMAT(LVISIT, ?) AS ID, PROFIT FROM B WHERE LVISIT >= ?
AND LVISIT <= ?AND LAST_AFFILATE LIKE ?
GROUP BY ID ORDER BY ID ' bind_values=<%b 2001-09-02 2001-11-16 %1556-%>
bind_types=<>
DB: Disconnect (id=3, numOpen = 0)
DB: Disconnect (id=2, numOpen = 0)
DB: Disconnect (id=1, numOpen = 0)
> > 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]
