list order from a hash
I have this table data that I load into a template as follows. $q = "SELECT ID, NAME FROM jobs ORDER BY NAME"; $sth = $dbh->prepare($q); $sth->execute(); my $Jobs_ref = $sth->fetchall_hashref('ID'); template 'sport_add.tt', { 'Jobs' => $Jobs_ref, 'Testing' => "This is text to test to make sure this will come through", # to test, use this like <% Testing %> on your template page }, {}; To display it in the template as a drop-down select I do: <select class="selectpicker" name="jobselect" id="jobselect" style="width: 500px;"> <% FOREACH ID IN Jobs.keys %> <option value="<% Jobs.$ID.ID %>"><% Jobs.$ID.NAME %></option> <% END %> And it works great. Except for one thing, the list is not in order by name. I have read more than a few posts about how once you save a query as a hashref that the order is lost. Most of those posts suggested fetching as a arrayref instead. However, I have no idea how to do that and then display it in a select like the above code does. So I wanted to see if somebody perhaps knows of a not too complicated of a solution to get the select list into alphabetical order by name. Thanks
On Wed, 2015-08-26 at 08:51 -0500, Richard Reina wrote:
I have this table data that I load into a template as follows.
$q = "SELECT ID, NAME FROM jobs ORDER BY NAME"; $sth = $dbh->prepare($q); $sth->execute(); my $Jobs_ref = $sth->fetchall_hashref('ID');
use fetchall_arrayref() instead.
To display it in the template as a drop-down select I do:
<select class="selectpicker" name="jobselect" id="jobselect" style="width: 500px;"> <% FOREACH ID IN Jobs.keys %>
Here you just need something like FOREACH job in Jobs
<option value="<% Jobs.$ID.ID %>"><% Jobs.$ID.NAME %></option>
And then here use job.ID, job.NAME etc.
And it works great. Except for one thing, the list is not in order by name. I have read more than a few posts about how once you save a query as a hashref that the order is lost.
Yes, in Perl arrays are ordered, hashes are not. If you want to retain order, you must use an array (or sort the hash before use, which is expensive).
Most of those posts suggested fetching as a arrayref instead. However, I have no idea how to do that and then display it in a select like the above code does.
See above.
Andrew and Attilla, Thank you for the replies. I am guessing that keeping the sorting in the SQL instead of the HTML would mean the page would load faster so I trying to go that route. However, when I switch my code above to: <% FOREACH job in Jobs %> <option value="<% job.ID %>"><% Job.NAME %></option> <% END %> I get an empty dropdown. I am using $sth->fetchall_arrayref(); with no arguments. Sorry to ask, but been playing with it for over an hour and still have it figured it out. 2015-08-26 9:04 GMT-05:00 Andrew Beverley <andy@andybev.com>:
On Wed, 2015-08-26 at 08:51 -0500, Richard Reina wrote:
I have this table data that I load into a template as follows.
$q = "SELECT ID, NAME FROM jobs ORDER BY NAME"; $sth = $dbh->prepare($q); $sth->execute(); my $Jobs_ref = $sth->fetchall_hashref('ID');
use fetchall_arrayref() instead.
To display it in the template as a drop-down select I do:
<select class="selectpicker" name="jobselect" id="jobselect" style="width: 500px;"> <% FOREACH ID IN Jobs.keys %>
Here you just need something like
FOREACH job in Jobs
<option value="<% Jobs.$ID.ID %>"><% Jobs.$ID.NAME %></option>
And then here use job.ID, job.NAME etc.
And it works great. Except for one thing, the list is not in order by name. I have read more than a few posts about how once you save a query as a hashref that the order is lost.
Yes, in Perl arrays are ordered, hashes are not. If you want to retain order, you must use an array (or sort the hash before use, which is expensive).
Most of those posts suggested fetching as a arrayref instead. However, I have no idea how to do that and then display it in a select like the above code does.
See above.
_______________________________________________ dancer-users mailing list dancer-users@dancer.pm http://lists.preshweb.co.uk/mailman/listinfo/dancer-users
Hey, I’m using hash and sorting it from the template toolkit. I would advise to try it then later you can play with the speed. Best regards, Attila
On 26 Aug 2015, at 19:39, Richard Reina <gatorreina@gmail.com> wrote:
Andrew and Attilla,
Thank you for the replies. I am guessing that keeping the sorting in the SQL instead of the HTML would mean the page would load faster so I trying to go that route.
However, when I switch my code above to:
<% FOREACH job in Jobs %> <option value="<% job.ID %>"><% Job.NAME %></option> <% END %>
I get an empty dropdown. I am using $sth->fetchall_arrayref(); with no arguments. Sorry to ask, but been playing with it for over an hour and still have it figured it out.
2015-08-26 9:04 GMT-05:00 Andrew Beverley <andy@andybev.com <mailto:andy@andybev.com>>: On Wed, 2015-08-26 at 08:51 -0500, Richard Reina wrote:
I have this table data that I load into a template as follows.
$q = "SELECT ID, NAME FROM jobs ORDER BY NAME"; $sth = $dbh->prepare($q); $sth->execute(); my $Jobs_ref = $sth->fetchall_hashref('ID');
use fetchall_arrayref() instead.
To display it in the template as a drop-down select I do:
<select class="selectpicker" name="jobselect" id="jobselect" style="width: 500px;"> <% FOREACH ID IN Jobs.keys %>
Here you just need something like
FOREACH job in Jobs
<option value="<% Jobs.$ID.ID <http://id.id/> %>"><% Jobs.$ID.NAME <http://id.name/> %></option>
And then here use job.ID, job.NAME etc.
And it works great. Except for one thing, the list is not in order by name. I have read more than a few posts about how once you save a query as a hashref that the order is lost.
Yes, in Perl arrays are ordered, hashes are not. If you want to retain order, you must use an array (or sort the hash before use, which is expensive).
Most of those posts suggested fetching as a arrayref instead. However, I have no idea how to do that and then display it in a select like the above code does.
See above.
_______________________________________________ dancer-users mailing list dancer-users@dancer.pm <mailto:dancer-users@dancer.pm> http://lists.preshweb.co.uk/mailman/listinfo/dancer-users <http://lists.preshweb.co.uk/mailman/listinfo/dancer-users>
_______________________________________________ dancer-users mailing list dancer-users@dancer.pm http://lists.preshweb.co.uk/mailman/listinfo/dancer-users
Hi Attila, <% FOREACH ID IN Jobs.keys.sort %> but multi-word lists do not appear exactly alphabetical as they do when MySQL sorts them by name. 2015-08-26 12:43 GMT-05:00 Attila Bárdi <attila.bardi@gmail.com>:
Hey,
I’m using hash and sorting it from the template toolkit. I would advise to try it then later you can play with the speed.
Best regards, Attila
On 26 Aug 2015, at 19:39, Richard Reina <gatorreina@gmail.com> wrote:
Andrew and Attilla,
Thank you for the replies. I am guessing that keeping the sorting in the SQL instead of the HTML would mean the page would load faster so I trying to go that route.
However, when I switch my code above to:
<% FOREACH job in Jobs %> <option value="<% job.ID %>"><% Job.NAME %></option> <% END %>
I get an empty dropdown. I am using $sth->fetchall_arrayref(); with no arguments. Sorry to ask, but been playing with it for over an hour and still have it figured it out.
2015-08-26 9:04 GMT-05:00 Andrew Beverley <andy@andybev.com>:
On Wed, 2015-08-26 at 08:51 -0500, Richard Reina wrote:
I have this table data that I load into a template as follows.
$q = "SELECT ID, NAME FROM jobs ORDER BY NAME"; $sth = $dbh->prepare($q); $sth->execute(); my $Jobs_ref = $sth->fetchall_hashref('ID');
use fetchall_arrayref() instead.
To display it in the template as a drop-down select I do:
<select class="selectpicker" name="jobselect" id="jobselect" style="width: 500px;"> <% FOREACH ID IN Jobs.keys %>
Here you just need something like
FOREACH job in Jobs
<option value="<% Jobs.$ID.ID <http://id.id/> %>"><% Jobs.$
ID.NAME <http://id.name/> %></option>
And then here use job.ID, job.NAME etc.
And it works great. Except for one thing, the list is not in order by name. I have read more than a few posts about how once you save a query as a hashref that the order is lost.
Yes, in Perl arrays are ordered, hashes are not. If you want to retain order, you must use an array (or sort the hash before use, which is expensive).
Most of those posts suggested fetching as a arrayref instead. However, I have no idea how to do that and then display it in a select like the above code does.
See above.
_______________________________________________ dancer-users mailing list dancer-users@dancer.pm http://lists.preshweb.co.uk/mailman/listinfo/dancer-users
_______________________________________________ dancer-users mailing list dancer-users@dancer.pm http://lists.preshweb.co.uk/mailman/listinfo/dancer-users
_______________________________________________ dancer-users mailing list dancer-users@dancer.pm http://lists.preshweb.co.uk/mailman/listinfo/dancer-users
On Wed, Aug 26, 2015 at 9:09 PM, Richard Reina <gatorreina@gmail.com> wrote:
Hi Attila,
<% FOREACH ID IN Jobs.keys.sort %> [KB] This definition sorts data by ID, not by name, because keys are ID,not name.
There are 2 ways 2 sort by name 1-) use arrayref instead of hashref because arrays stores data with the order you got from sql 2-) hashes are not ordered, At Perl code I do it like this foreach (sort { $Jobs{a} cmp $Jobs{b} }) { } I if you want to pass hashref to template and want to sort by name I am not sure if template engine supports it but ther should be corresponding of it. For example, there may be sth like below: <% FOREACH ID IN Jobs.values.sort %> I only predicted about it :)
but multi-word lists do not appear exactly alphabetical as they do when MySQL sorts them by name.
2015-08-26 12:43 GMT-05:00 Attila Bárdi <attila.bardi@gmail.com>:
Hey,
I’m using hash and sorting it from the template toolkit. I would advise to try it then later you can play with the speed.
Best regards, Attila
On 26 Aug 2015, at 19:39, Richard Reina <gatorreina@gmail.com> wrote:
Andrew and Attilla,
Thank you for the replies. I am guessing that keeping the sorting in the SQL instead of the HTML would mean the page would load faster so I trying to go that route.
However, when I switch my code above to:
<% FOREACH job in Jobs %> <option value="<% job.ID %>"><% Job.NAME %></option> <% END %>
I get an empty dropdown. I am using $sth->fetchall_arrayref(); with no arguments. Sorry to ask, but been playing with it for over an hour and still have it figured it out.
2015-08-26 9:04 GMT-05:00 Andrew Beverley <andy@andybev.com>:
On Wed, 2015-08-26 at 08:51 -0500, Richard Reina wrote:
I have this table data that I load into a template as follows.
$q = "SELECT ID, NAME FROM jobs ORDER BY NAME"; $sth = $dbh->prepare($q); $sth->execute(); my $Jobs_ref = $sth->fetchall_hashref('ID');
use fetchall_arrayref() instead.
To display it in the template as a drop-down select I do:
<select class="selectpicker" name="jobselect" id="jobselect" style="width: 500px;"> <% FOREACH ID IN Jobs.keys %>
Here you just need something like
FOREACH job in Jobs
<option value="<% Jobs.$ID.ID %>"><% Jobs.$ID.NAME %></option>
And then here use job.ID, job.NAME etc.
And it works great. Except for one thing, the list is not in order by name. I have read more than a few posts about how once you save a query as a hashref that the order is lost.
Yes, in Perl arrays are ordered, hashes are not. If you want to retain order, you must use an array (or sort the hash before use, which is expensive).
Most of those posts suggested fetching as a arrayref instead. However, I have no idea how to do that and then display it in a select like the above code does.
See above.
_______________________________________________ dancer-users mailing list dancer-users@dancer.pm http://lists.preshweb.co.uk/mailman/listinfo/dancer-users
_______________________________________________ dancer-users mailing list dancer-users@dancer.pm http://lists.preshweb.co.uk/mailman/listinfo/dancer-users
_______________________________________________ dancer-users mailing list dancer-users@dancer.pm http://lists.preshweb.co.uk/mailman/listinfo/dancer-users
_______________________________________________ dancer-users mailing list dancer-users@dancer.pm http://lists.preshweb.co.uk/mailman/listinfo/dancer-users
-- Kadir Beyazlı Computer Engineer GSM : +90 535 821 50 00
On Wed, 2015-08-26 at 12:39 -0500, Richard Reina wrote:
I have this table data that I load into a template as follows.
$q = "SELECT ID, NAME FROM jobs ORDER BY NAME"; $sth = $dbh->prepare($q); $sth->execute(); my $Jobs_ref = $sth->fetchall_hashref('ID');
use fetchall_arrayref() instead.
To display it in the template as a drop-down select I do:
<select class="selectpicker" name="jobselect" id="jobselect" style="width: 500px;"> <% FOREACH ID IN Jobs.keys %>
Here you just need something like
FOREACH job in Jobs
<option value="<% Jobs.$ID.ID %>"><% Jobs.$ID.NAME %></option>
And then here use job.ID, job.NAME etc.
Thank you for the replies. I am guessing that keeping the sorting in the SQL instead of the HTML would mean the page would load faster so I trying to go that route.
However, when I switch my code above to:
<% FOREACH job in Jobs %> <option value="<% job.ID %>"><% Job.NAME %></option> <% END %>
I get an empty dropdown. I am using $sth->fetchall_arrayref(); with no arguments.
You might need to do: $sth->fetchall_arrayref({}) Sorry, it's been a while since I've done direct DBI calls. I'd recommend checking out DBIx::Class if you have the time. If you're still not having any luck, dump the result to see what you've got: use Data::Dumper; say STDERR Dumper $Jobs_ref; Be careful of case-sensitivity, that could cause you problems too. Andy
Hi Andrew, the {} did the trick. Thank you VERY much. Was going out of my mind. 2015-08-26 13:04 GMT-05:00 Andrew Beverley <andy@andybev.com>:
On Wed, 2015-08-26 at 12:39 -0500, Richard Reina wrote:
I have this table data that I load into a template as follows.
$q = "SELECT ID, NAME FROM jobs ORDER BY NAME"; $sth = $dbh->prepare($q); $sth->execute(); my $Jobs_ref = $sth->fetchall_hashref('ID');
use fetchall_arrayref() instead.
To display it in the template as a drop-down select I do:
<select class="selectpicker" name="jobselect" id="jobselect" style="width: 500px;"> <% FOREACH ID IN Jobs.keys %>
Here you just need something like
FOREACH job in Jobs
<option value="<% Jobs.$ID.ID %>"><% Jobs.$ID.NAME
%></option>
And then here use job.ID, job.NAME etc.
Thank you for the replies. I am guessing that keeping the sorting in the SQL instead of the HTML would mean the page would load faster so I trying to go that route.
However, when I switch my code above to:
<% FOREACH job in Jobs %> <option value="<% job.ID %>"><% Job.NAME %></option> <% END %>
I get an empty dropdown. I am using $sth->fetchall_arrayref(); with no arguments.
You might need to do:
$sth->fetchall_arrayref({})
Sorry, it's been a while since I've done direct DBI calls. I'd recommend checking out DBIx::Class if you have the time.
If you're still not having any luck, dump the result to see what you've got:
use Data::Dumper; say STDERR Dumper $Jobs_ref;
Be careful of case-sensitivity, that could cause you problems too.
Andy
_______________________________________________ dancer-users mailing list dancer-users@dancer.pm http://lists.preshweb.co.uk/mailman/listinfo/dancer-users
Hey, use the manual Luke!:) http://www.template-toolkit.org/docs/manual/Directives.html <http://www.template-toolkit.org/docs/manual/Directives.html> Search for sort. <% FOREACH ID IN Jobs.keys.sort %> <% FOREACH ID IN Jobs.keys.nsort %> sorted alphabetically ( sort ) or numerically ( nsort ) Best regards, Attila
On 26 Aug 2015, at 15:51, Richard Reina <gatorreina@gmail.com> wrote:
I have this table data that I load into a template as follows.
$q = "SELECT ID, NAME FROM jobs ORDER BY NAME"; $sth = $dbh->prepare($q); $sth->execute(); my $Jobs_ref = $sth->fetchall_hashref('ID');
template 'sport_add.tt <http://sport_add.tt/>', {
'Jobs' => $Jobs_ref, 'Testing' => "This is text to test to make sure this will come through", # to test, use this like <% Testing %> on your template page
}, {};
To display it in the template as a drop-down select I do:
<select class="selectpicker" name="jobselect" id="jobselect" style="width: 500px;"> <% FOREACH ID IN Jobs.keys %> <option value="<% Jobs.$ID.ID <http://id.id/> %>"><% Jobs.$ID.NAME <http://id.name/> %></option> <% END %>
And it works great. Except for one thing, the list is not in order by name. I have read more than a few posts about how once you save a query as a hashref that the order is lost. Most of those posts suggested fetching as a arrayref instead. However, I have no idea how to do that and then display it in a select like the above code does. So I wanted to see if somebody perhaps knows of a not too complicated of a solution to get the select list into alphabetical order by name.
Thanks _______________________________________________ dancer-users mailing list dancer-users@dancer.pm http://lists.preshweb.co.uk/mailman/listinfo/dancer-users
Hi! 2015-08-26 16:51 GMT+03:00 Richard Reina <gatorreina@gmail.com>:
I have this table data that I load into a template as follows.
$q = "SELECT ID, NAME FROM jobs ORDER BY NAME";
Here you may just query with one line: my $Jobs_ref = $dbh->selectall_arrayref( $q, { Slice => {} } ); And now in template <% FOREACH job IN Jobs %> <option value="<% job.ID %>"><% job.NAME %></option> <% END %> So you will use exact order, like you got from DB. Wbr, -- Kõike hääd, Gunnar
participants (5)
-
Andrew Beverley -
Attila Bárdi -
Kadir Beyazlı -
Richard Reina -
WK