Hello All, I ussually iterate a hash that is the result of $rows_ref = $sth->fetchall_hashref('ID'); in my template with <% FOREACH ID Matched_pats.keys.sort %>. However, In this case though the template only displays one row because each resulting row in the SQL query all have the same ID number. Is there any simple change I can make to get the results to fully iterate? Thanks
Hi! 2015-09-30 22:20 GMT+03:00 Richard Reina <gatorreina@gmail.com>:
Matched_pats.keys.sort %>. However, In this case though the template only displays one row because each resulting row in the SQL query all have the same ID number. Is there any simple change I can make to get the results to fully iterate?
How could you have hash(ref), where are multiple keys named by same ID? Could you show your datastructure? Wbr, -- Kõike hääd, Gunnar
2015-09-30 14:25 GMT-05:00 WK <wanradt@gmail.com>:
Hi!
2015-09-30 22:20 GMT+03:00 Richard Reina <gatorreina@gmail.com>:
Matched_pats.keys.sort %>. However, In this case though the template only displays one row because each resulting row in the SQL query all have the same ID number. Is there any simple change I can make to get the results to fully iterate?
How could you have hash(ref), where are multiple keys named by same ID?
Could you show your datastructure?
$q = 'SELECT l.ID, c.ID AS CHLG_ID, c.season, c.YEAR, g.date, g.opp_team, a.NAME AS ANAME, ch.NAME AS CHNAME, p.AMOUNT, u.screenname AS SPONSOR FROM game g, accomp_type a, logro l LEFT JOIN challenge c ON l.ACC_TYPE_ID=c.ACC_TYPE_ID LEFT JOIN patrocinio p ON c.ID=p.CHLNG_ID LEFT JOIN charity ch ON c.CHAR_ID=ch.ID LEFT JOIN patron pn ON p.PTRN_ID=pn.ID LEFT JOIN usuario u ON pn.USER_ID=u.ID WHERE l.GAME_ID=? AND l.status=? AND g.ID=l.GAME_ID AND YEAR(g.DATE)=c.YEAR AND MONTH(g.date) IN (?, ?, ?) AND l.ACC_TYPE_ID=a.ID';
$sth = $dbh->prepare($q); $sth->execute($self->{_game_id}, $self->{_logro_status}, @$season_mo[0], @$season_mo[1], @$season_mo[2]); my $pats_ref = $sth->fetchall_hashref('ID');
On Wed, Sep 30, 2015 at 9:36 PM, Richard Reina <gatorreina@gmail.com> wrote:
2015-09-30 14:25 GMT-05:00 WK <wanradt@gmail.com>:
Hi!
2015-09-30 22:20 GMT+03:00 Richard Reina <gatorreina@gmail.com>:
Matched_pats.keys.sort %>. However, In this case though the template only displays one row because each resulting row in the SQL query all have the same ID number. Is there any simple change I can make to get the results to fully iterate?
How could you have hash(ref), where are multiple keys named by same ID?
Could you show your datastructure?
$q = 'SELECT l.ID, c.ID AS CHLG_ID, c.season, c.YEAR, g.date, g.opp_team, a.NAME AS ANAME, ch.NAME AS CHNAME, p.AMOUNT, u.screenname AS SPONSOR FROM game g, accomp_type a, logro l LEFT JOIN challenge c ON l.ACC_TYPE_ID=c.ACC_TYPE_ID LEFT JOIN patrocinio p ON c.ID=p.CHLNG_ID LEFT JOIN charity ch ON c.CHAR_ID=ch.ID LEFT JOIN patron pn ON p.PTRN_ID=pn.ID LEFT JOIN usuario u ON pn.USER_ID=u.ID WHERE l.GAME_ID=? AND l.status=? AND g.ID=l.GAME_ID AND YEAR(g.DATE)=c.YEAR AND MONTH(g.date) IN (?, ?, ?) AND l.ACC_TYPE_ID=a.ID';
$sth = $dbh->prepare($q); $sth->execute($self->{_game_id}, $self->{_logro_status}, @$season_mo[0], @$season_mo[1], @$season_mo[2]); my $pats_ref = $sth->fetchall_hashref('ID');
Wouldn't be it easier to see the structure of the hash with Data::Dumper? Could you please paste the output of debug Dumper($pats_ref); ?
Sorry but not familiar with Dumper. print "debug Dumper($pats_ref)\n"; debug Dumper(HASH(0x9d5e1b8))
Just look it up on metacpan.org, it is very useful for debugging. with debug you can log something in the log file, I usually start a dancer app with plackup -a /bin/app.(psgi|pl) and monitor what is happening in the output. if you prefer to use print, then you need to write it this way: print Dumper($pats_ref); thought print will output to the browser which is not very optimal. Also you can use Dumper with Template::Toolkit http://www.template-toolkit.org/docs/modules/Template/Plugin/Dumper.html On Wed, Sep 30, 2015 at 9:53 PM, Richard Reina <gatorreina@gmail.com> wrote:
Sorry but not familiar with Dumper.
print "debug Dumper($pats_ref)\n";
debug Dumper(HASH(0x9d5e1b8))
_______________________________________________ dancer-users mailing list dancer-users@dancer.pm http://lists.preshweb.co.uk/mailman/listinfo/dancer-users
2015-09-30 15:17 GMT-05:00 Alex Mestiashvili <mailatgoogl@gmail.com>:
Just look it up on metacpan.org, it is very useful for debugging.
with debug you can log something in the log file, I usually start a dancer app with plackup -a /bin/app.(psgi|pl) and monitor what is happening in the output.
if you prefer to use print, then you need to write it this way:
print Dumper($pats_ref);
Well something somewhat strange has happened. When I do: print Dumper($pats_ref) . "\n"; from the perl module that handles the DB query it only prints one row: $VAR1 = { '8' => { 'ID' => '8', 'ANAME' => 'KOB', 'opp_team' => 'Cardinals', 'YEAR' => '2015', 'date' => '2015-09-04', 'AMOUNT' => '2.00', 'IDD' => '3', 'CHNAME' => 'DWB', 'CHLG_ID' => '12', 'SPONSOR' => 'MPK', 'season' => 'Fall' } }; This is baffling and very frustrating because when I do the exact same query from the MySQL console it yields 3 row and not 1 row. ID | CHLG_ID | season | YEAR | date | opp_team | NAME | NAME | AMOUNT | sponsor | +----+---------+--------+------+------------+----------+-----------+-----------------------------+--------+---------+------------- | 8 | 9 | Fall | 2015 | 2015-09-04 | Cardinals | KOB | AFA | 2.00 | MPK | | 8 | 12 | Fall | 2015 | 2015-09-04 | Cardinals | KOB | DWB | 1.00 | RFR | | 8 | 12 | Fall | 2015 | 2015-09-04 | Cardinals | KOB | DWB | 2.00 | MPK | +----+---------+--------+------+------------+----------+-----------+-----------------------------+--------+---------+ Any insight as to why this might be happening would be greatly, greatly appreciated.
On Wed, Sep 30, 2015 at 2:41 PM, Richard Reina <gatorreina@gmail.com> wrote:
Well something somewhat strange has happened. When I do: print Dumper($pats_ref) . "\n"; from the perl module that handles the DB query it only prints one row:
$VAR1 = { '8' => { 'ID' => '8', 'ANAME' => 'KOB', 'opp_team' => 'Cardinals', 'YEAR' => '2015', 'date' => '2015-09-04', 'AMOUNT' => '2.00', 'IDD' => '3', 'CHNAME' => 'DWB', 'CHLG_ID' => '12', 'SPONSOR' => 'MPK', 'season' => 'Fall' } };
This is baffling and very frustrating because when I do the exact same query from the MySQL console it yields 3 row and not 1 row.
ID | CHLG_ID | season | YEAR | date | opp_team | NAME | NAME | AMOUNT | sponsor | +----+---------+--------+------+------------+----------+-----------+-----------------------------+--------+---------+------------- | 8 | 9 | Fall | 2015 | 2015-09-04 | Cardinals | KOB | AFA | 2.00 | MPK | | 8 | 12 | Fall | 2015 | 2015-09-04 | Cardinals | KOB | DWB | 1.00 | RFR | | 8 | 12 | Fall | 2015 | 2015-09-04 | Cardinals | KOB | DWB | 2.00 | MPK | +----+---------+--------+------+------------+----------+-----------+-----------------------------+--------+---------+
Any insight as to why this might be happening would be greatly, greatly appreciated.
You are telling DBI to put the results in a hash by ID. So later rows overwrite earlier ones. You probably want fetchall_arrayref( {} ) instead of fetchall_hashref, but I don't remember seeing what you want to end up doing with the data.
As far as I know you can do something like this: $rows_ref = $sth->fetchall_hashref( ['ID', 'AnotherID' ] ) To specify multiple ID's to be unique by, as I've had that same issue in the past. Then do a data Dumper to see what the structure looks like. David On Thu, Oct 1, 2015 at 8:30 AM, Yitzchak Scott-Thoennes <sthoenna@gmail.com> wrote:
On Wed, Sep 30, 2015 at 2:41 PM, Richard Reina <gatorreina@gmail.com> wrote:
Well something somewhat strange has happened. When I do: print Dumper($pats_ref) . "\n"; from the perl module that handles the DB query it only prints one row:
$VAR1 = { '8' => { 'ID' => '8', 'ANAME' => 'KOB', 'opp_team' => 'Cardinals', 'YEAR' => '2015', 'date' => '2015-09-04', 'AMOUNT' => '2.00', 'IDD' => '3', 'CHNAME' => 'DWB', 'CHLG_ID' => '12', 'SPONSOR' => 'MPK', 'season' => 'Fall' } };
This is baffling and very frustrating because when I do the exact same query from the MySQL console it yields 3 row and not 1 row.
ID | CHLG_ID | season | YEAR | date | opp_team | NAME | NAME | AMOUNT | sponsor |
+----+---------+--------+------+------------+----------+-----------+-----------------------------+--------+---------+-------------
| 8 | 9 | Fall | 2015 | 2015-09-04 | Cardinals | KOB | AFA | 2.00 | MPK | | 8 | 12 | Fall | 2015 | 2015-09-04 | Cardinals | KOB | DWB | 1.00 | RFR | | 8 | 12 | Fall | 2015 | 2015-09-04 | Cardinals | KOB | DWB | 2.00 | MPK |
+----+---------+--------+------+------------+----------+-----------+-----------------------------+--------+---------+
Any insight as to why this might be happening would be greatly, greatly appreciated.
You are telling DBI to put the results in a hash by ID. So later rows overwrite earlier ones.
You probably want fetchall_arrayref( {} ) instead of fetchall_hashref, but I don't remember seeing what you want to end up doing with the data. _______________________________________________ dancer-users mailing list dancer-users@dancer.pm http://lists.preshweb.co.uk/mailman/listinfo/dancer-users
2015-09-30 18:00 GMT-05:00 Yitzchak Scott-Thoennes <sthoenna@gmail.com>:
On Wed, Sep 30, 2015 at 2:41 PM, Richard Reina <gatorreina@gmail.com> wrote:
Well something somewhat strange has happened. When I do: print Dumper($pats_ref) . "\n"; from the perl module that handles the DB query it only prints one row:
$VAR1 = { '8' => { 'ID' => '8', 'ANAME' => 'KOB', 'opp_team' => 'Cardinals', 'YEAR' => '2015', 'date' => '2015-09-04', 'AMOUNT' => '2.00', 'IDD' => '3', 'CHNAME' => 'DWB', 'CHLG_ID' => '12', 'SPONSOR' => 'MPK', 'season' => 'Fall' } };
This is baffling and very frustrating because when I do the exact same query from the MySQL console it yields 3 row and not 1 row.
ID | CHLG_ID | season | YEAR | date | opp_team | NAME | NAME | AMOUNT | sponsor |
+----+---------+--------+------+------------+----------+-----------+-----------------------------+--------+---------+-------------
| 8 | 9 | Fall | 2015 | 2015-09-04 | Cardinals | KOB | AFA | 2.00 | MPK | | 8 | 12 | Fall | 2015 | 2015-09-04 | Cardinals | KOB | DWB | 1.00 | RFR | | 8 | 12 | Fall | 2015 | 2015-09-04 | Cardinals | KOB | DWB | 2.00 | MPK |
+----+---------+--------+------+------------+----------+-----------+-----------------------------+--------+---------+
Any insight as to why this might be happening would be greatly, greatly appreciated.
You are telling DBI to put the results in a hash by ID. So later rows overwrite earlier ones.
You probably want fetchall_arrayref( {} ) instead of fetchall_hashref, but I don't remember seeing what you want to end up doing with the data.
Yitzchak, I cannot thank you enough for your suggestion. Yes, fetchall_arrayref( {} ) worked perfectly. I still do not have a good understanding as to when to use fetchall_hashref and when to opt for fecthall_arrayref() instead. I really really appreicate your help in getting me straightened out. Thank you again.
Correction on my syntax, for reference. $sth = $dbh->prepare("SELECT foo, bar, baz FROM table");$sth->execute;$hash_ref = $sth->fetchall_hashref( [ qw <http://perldoc.perl.org/functions/qw.html>(foo bar) ] );print <http://perldoc.perl.org/functions/print.html> "For foo 42 and bar 38, baz is $hash_ref->{42}->{38}->{baz}\n"; Almost! On Thu, Oct 1, 2015 at 9:35 AM, Richard Reina <gatorreina@gmail.com> wrote:
2015-09-30 18:00 GMT-05:00 Yitzchak Scott-Thoennes <sthoenna@gmail.com>:
On Wed, Sep 30, 2015 at 2:41 PM, Richard Reina <gatorreina@gmail.com> wrote:
Well something somewhat strange has happened. When I do: print Dumper($pats_ref) . "\n"; from the perl module that handles the DB query it only prints one row:
$VAR1 = { '8' => { 'ID' => '8', 'ANAME' => 'KOB', 'opp_team' => 'Cardinals', 'YEAR' => '2015', 'date' => '2015-09-04', 'AMOUNT' => '2.00', 'IDD' => '3', 'CHNAME' => 'DWB', 'CHLG_ID' => '12', 'SPONSOR' => 'MPK', 'season' => 'Fall' } };
This is baffling and very frustrating because when I do the exact same query from the MySQL console it yields 3 row and not 1 row.
ID | CHLG_ID | season | YEAR | date | opp_team | NAME | NAME | AMOUNT | sponsor |
+----+---------+--------+------+------------+----------+-----------+-----------------------------+--------+---------+-------------
| 8 | 9 | Fall | 2015 | 2015-09-04 | Cardinals | KOB | AFA | 2.00 | MPK | | 8 | 12 | Fall | 2015 | 2015-09-04 | Cardinals | KOB | DWB | 1.00 | RFR | | 8 | 12 | Fall | 2015 | 2015-09-04 | Cardinals | KOB | DWB | 2.00 | MPK |
+----+---------+--------+------+------------+----------+-----------+-----------------------------+--------+---------+
Any insight as to why this might be happening would be greatly, greatly appreciated.
You are telling DBI to put the results in a hash by ID. So later rows overwrite earlier ones.
You probably want fetchall_arrayref( {} ) instead of fetchall_hashref, but I don't remember seeing what you want to end up doing with the data.
Yitzchak,
I cannot thank you enough for your suggestion. Yes, fetchall_arrayref( {} ) worked perfectly. I still do not have a good understanding as to when to use fetchall_hashref and when to opt for fecthall_arrayref() instead. I really really appreicate your help in getting me straightened out.
Thank you again.
_______________________________________________ dancer-users mailing list dancer-users@dancer.pm http://lists.preshweb.co.uk/mailman/listinfo/dancer-users
2015-09-30 22:20 GMT+03:00 Richard Reina <gatorreina@gmail.com>:
Matched_pats.keys.sort %>. However, In this case though the template only displays one row because each resulting row in the SQL query all have the same ID number. Is there any simple change I can make to get the results to fully iterate?
How could you have hash(ref), where are multiple keys named by same ID?
Could you show your datastructure?
$q = 'SELECT l.ID, c.ID AS CHLG_ID, c.season, c.YEAR, g.date, g.opp_team, a.NAME AS ANAME, ch.NAME AS CHNAME, p.AMOUNT, u.screenname AS SPONSOR FROM game g, accomp_type a, logro l LEFT JOIN challenge c ON l.ACC_TYPE_ID=c.ACC_TYPE_ID LEFT JOIN patrocinio p ON c.ID=p.CHLNG_ID LEFT JOIN charity ch ON c.CHAR_ID=ch.ID LEFT JOIN patron pn ON p.PTRN_ID=pn.ID LEFT JOIN usuario u ON pn.USER_ID=u.ID WHERE l.GAME_ID=? AND l.status=? AND g.ID=l.GAME_ID AND YEAR(g.DATE)=c.YEAR AND MONTH(g.date) IN (?, ?, ?) AND l.ACC_TYPE_ID=a.ID';
$sth = $dbh->prepare($q); $sth->execute($self->{_game_id}, $self->{_logro_status}, @$season_mo[0], @$season_mo[1], @$season_mo[2]); my $pats_ref = $sth->fetchall_hashref('ID');
Hit send too soon by accident. Above is sql query that the hash reference comes from.
Richard> Hello All, I ussually iterate a hash that is the result of Richard> $rows_ref = $sth->fetchall_hashref('ID'); in my template Richard> with <% FOREACH ID Matched_pats.keys.sort %>. However, In Richard> this case though the template only displays one row because Richard> each resulting row in the SQL query all have the same ID Richard> number. Is there any simple change I can make to get the Richard> results to fully iterate? What you generally need to do in this case is do multiple queries, or you need to redo your structure so that you key off something else. For example, if each user can have one or more charities, you want to print out something like: User: John Charity: RedCross NPR Station Local Park Big Park across county User: Richard Charity: Dancer User: Bob User: Steve Charity: RedCross So youre temply would loop over the users, then over the charity field for each user. It's the One->Many relationship printing. The inefficient way is to do a select for all the users, then a query per-user for each charity they have. Or as you've shown, doing a big join also works. But when you want to paginate your results, doing a limit as early as possible might make alot of sense to save crawls through the DB. So the template would do something like: <% FOREACH NAME .... %> User: <% name %> <% FOREACH Charity NAME->.... %> Charity: <% Charity %> <% END %> <% END %> But my syntax is completly off since I don't know TT off the top of my head. John
Quoting Richard Reina <gatorreina@gmail.com>:
Hello All,
I ussually iterate a hash that is the result of $rows_ref = $sth->fetchall_hashref('ID'); in my template with <% FOREACH ID Matched_pats.keys.sort %>. However, In this case though the template only displays one row because each resulting row in the SQL query all have the same ID number. Is there any simple change I can make to get the results to fully iterate?
If you're putting data into a hash that's keyed on ID and every row has the same ID then you will only ever have one entry in your hash as each row that DBI puts into the hash will overwrite the previous one with the same ID. It's the same as writing code like this: my $rows_ref = {}; $rows_ref->{'an_ID'} = 'foo'; $rows_ref->{'an_ID'} = 'bar'; $rows_ref->{'an_ID'} = 'baz'; I hope it's obvious that this hash will only ever contain one key/value pair. Using fetchall_hashref('ID') with a constant value for 'ID' has exactly the same effect. As others have pointed out, the solution is to use a fetch method which returns an array rather than a hash. I'd add that in my experience (and I've been doing this a very long time) returning database data in an array (or an array of arrays) is almost always a better idea than returning a hash. Hashes certainly have their uses (that is, after all, why those methods exist), but arrays seem to match most people's model of a database resultset better than hashes do. In fact, now I come to think about it, the other problem that I helped you with recently was only as complex as it was because your data structure was a hash. In my solution, the first thing I did was to call values() on the hash which effectively turned it into an array. So I'd highly recommend using arrays rather than hashes in most instances when you're dealing with database resultsets. I'd also like to point out that not all of your problems are Dancer problems. The people on this list are, of course, really helpful. But the Perl community has mailing lists for all sorts of things. In particular, you might consider joining the mailing lists for: * DBI - http://lists.perl.org/list/dbi-users.html * TT - http://lists.perl.org/list/template-toolkit.html Hope this is helpful, Dave...
Quoting Dave Cross <dave@dave.org.uk>:
So I'd highly recommend using arrays rather than hashes in most instances when you're dealing with database resultsets.
I should clarify this. The data for an individual row in a resultset can usefully be stored in either an array or a hash. But those records should usually be linked together in an array, not a hash. Dave...
2015-10-01 4:00 GMT-05:00 Dave Cross <dave@dave.org.uk>:
Quoting Richard Reina <gatorreina@gmail.com>:
Hello All,
I ussually iterate a hash that is the result of $rows_ref = $sth->fetchall_hashref('ID'); in my template with <% FOREACH ID Matched_pats.keys.sort %>. However, In this case though the template only displays one row because each resulting row in the SQL query all have the same ID number. Is there any simple change I can make to get the results to fully iterate?
If you're putting data into a hash that's keyed on ID and every row has the same ID then you will only ever have one entry in your hash as each row that DBI puts into the hash will overwrite the previous one with the same ID. It's the same as writing code like this:
my $rows_ref = {}; $rows_ref->{'an_ID'} = 'foo'; $rows_ref->{'an_ID'} = 'bar'; $rows_ref->{'an_ID'} = 'baz';
I hope it's obvious that this hash will only ever contain one key/value pair. Using fetchall_hashref('ID') with a constant value for 'ID' has exactly the same effect.
As others have pointed out, the solution is to use a fetch method which returns an array rather than a hash. I'd add that in my experience (and I've been doing this a very long time) returning database data in an array (or an array of arrays) is almost always a better idea than returning a hash. Hashes certainly have their uses (that is, after all, why those methods exist), but arrays seem to match most people's model of a database resultset better than hashes do.
In fact, now I come to think about it, the other problem that I helped you with recently was only as complex as it was because your data structure was a hash. In my solution, the first thing I did was to call values() on the hash which effectively turned it into an array.
So I'd highly recommend using arrays rather than hashes in most instances when you're dealing with database resultsets.
I'd also like to point out that not all of your problems are Dancer problems. The people on this list are, of course, really helpful. But the Perl community has mailing lists for all sorts of things. In particular, you might consider joining the mailing lists for:
* DBI - http://lists.perl.org/list/dbi-users.html * TT - http://lists.perl.org/list/template-toolkit.html
Hope this is helpful,
Dave...
Dave, Thank you for the very thorough and instructive explanation. I will take your advice to heart.
On Thu, Oct 1, 2015 at 2:00 AM, Dave Cross <dave@dave.org.uk> wrote:
I'd also like to point out that not all of your problems are Dancer problems. The people on this list are, of course, really helpful. But the Perl community has mailing lists for all sorts of things. In particular, you might consider joining the mailing lists for:
* DBI - http://lists.perl.org/list/dbi-users.html * TT - http://lists.perl.org/list/template-toolkit.html
And of course: http://www.perlmonks.org/?node=SOPW and http://stackoverflow.com/questions/ask
participants (7)
-
Alex Mestiashvili -
Dave Cross -
David H -
John Stoffel -
Richard Reina -
WK -
Yitzchak Scott-Thoennes