You are viewing a plain text version of this content. The canonical link for it is here.
Posted to embperl@perl.apache.org by "Alexei V. Alexandrov" <av...@elcomsoft.com> on 2001/11/16 12:24:47 UTC
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: embperl-unsubscribe@perl.apache.org
For additional commands, e-mail: embperl-help@perl.apache.org
Re: DBIx::Recordset linked tables?
Posted by Gerald Richter <ri...@ecos.de>.
The problem is that you are using the '$where' inside the !Links. The $where
as precedence and overides the condition that DBIx::Recordset creates to
link the tables. The solution would be to make the following modifications:
> > >
> > > *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',
'!Fields' =>
'DATE_FORMAT(LVISIT, '%b') 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-%'],
Instead of the $where and $values use
'LVISIT' => ['2001-09-02',
'2001-11-16'],
'*LVISIT' => ['>=', '<=>'],
'LAST_AFFILATE' => '%1556-%',
'*LAST_AFFILATE' => 'LIKE',
> > > '$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'
> > > });
> > >
Gerald
-------------------------------------------------------------
Gerald Richter ecos electronic communication services gmbh
Internetconnect * Webserver/-design/-datenbanken * Consulting
Post: Tulpenstrasse 5 D-55276 Dienheim b. Mainz
E-Mail: richter@ecos.de Voice: +49 6133 925131
WWW: http://www.ecos.de Fax: +49 6133 925152
-------------------------------------------------------------
---------------------------------------------------------------------
To unsubscribe, e-mail: embperl-unsubscribe@perl.apache.org
For additional commands, e-mail: embperl-help@perl.apache.org
Re: DBIx::Recordset linked tables?
Posted by "Alexei V. Alexandrov" <av...@elcomsoft.com>.
> 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: embperl-unsubscribe@perl.apache.org
> > For additional commands, e-mail: embperl-help@perl.apache.org
> >
> >
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: embperl-unsubscribe@perl.apache.org
For additional commands, e-mail: embperl-help@perl.apache.org
Re: DBIx::Recordset linked tables?
Posted by Gerald Richter <ri...@ecos.de>.
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: richter@ecos.de Voice: +49 6133 925131
WWW: http://www.ecos.de Fax: +49 6133 925152
-------------------------------------------------------------
----- Original Message -----
From: "Alexei V. Alexandrov" <av...@elcomsoft.com>
To: <em...@perl.apache.org>
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: embperl-unsubscribe@perl.apache.org
> For additional commands, e-mail: embperl-help@perl.apache.org
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: embperl-unsubscribe@perl.apache.org
For additional commands, e-mail: embperl-help@perl.apache.org