complex hash sort

gamiensrule has asked for the wisdom of the Perl Monks concerning the following question:

I have a DBI query...

$get = $dbh->prepare("SELECT * FROM clients ORDER BY lastname");
$get->execute;
[download]

returning a hash with...

$clients = $get->fetchall_hashref('id');
[download]

So obviously, the value for each key in the hash is the corresponding row from the database. I get an array of the keys (each rows 'id' field)...

@list = keys(%{$clients});
[download]

but I need to sort that array by fields in the returned data, not the @list elements themselves. For instance, $clients{'1'}->{'lastname'}. However, by pulling the data with fetchall_hashref, the sorted results are lossed. Any ideas?

Comment on complex hash sort Select or Download Code

Replies are listed 'Best First'.
Re: complex hash sort by roboticus (Chancellor) on Feb 11, 2011 at 21:14 UTC
gamiensrule: Assuming last_name is the column you want to sort on, you should be able to use: `my $clients = $get->fetchall_hashref('id'); my @list = keys %$clients; my @sorted_list = sort { $$clients{$a}{last_name} cmp $$clients{$b}{la +st_name} } @list;` [download] Remember to replace `cmp` with `<=>` for numeric fields. ...roboticus When your only tool is a hammer, all problems look like your thumb. Update: After re-reading OP, I replaced SORT_KEY with last_name, to match his problem statement.	[reply] [d/l] [select]
Re: complex hash sort by revolet (Initiate) on Feb 11, 2011 at 22:02 UTC
I'm curious if you are required to use fetchall_hashref or if you can use fetchall_arrayref instead. For example, to get an array of hashes containing the rows in the same order they are produced by your query, you could do this: `my $clients = $get->fetchall_arrayref({});` [download] Now $clients is a reference to an array, sorted by last name, where each element is a hash containing the row data. If you want a list of IDs ordered by last name: `my @ids = map { $_->{id} } @$clients;` [download] If you want a hash that maps IDs to clients: `my %client_for = map { $_->{id} => $_ } @$clients; # $client_for->{1}->{lastname} ...` [download] Let me know if this helps and if you have any more questions.	[reply] [d/l] [select]