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]
