I am using Perl dancer2 to write an application. Running select query in mysql displays all records. But the same query run in dancer2 and template toolkit displays only unique records.
Eg. 34 records fetched when this is run in mysql client.
select timing.time as Time,
church.church_name as Church_Name,
church.church_address as Address,
language.language_name as Language,
denomination.denomination_name as Denomination,
city.city_name as City,
state.state_name as State,
country.country_name as Country
from church
join country on church.church_countryid=country_id
join state on church.church_stateid=state.state_id
join city on church.church_cityid=city.city_id
join church_mass_timing on church.church_id=church_mass_timing.church_id
join timing on church_mass_timing.time_id=timing.time_id
join language on church_mass_timing.language_id=language.language_id
join denomination on church.church_denominationid=denomination.denomination_id
order by church.church_name,
timing.time;
Same query in Dancer with Template Toolkit returns 11 records.
get '/church_list' => sub {
my $db = connect_db();
my $sql='select timing.time as Time,
church.church_name as Church_Name,
church.church_address as Address,
language.language_name as Language,
denomination.denomination_name as Denomination,
city.city_name as City,
state.state_name as State,
country.country_name as Country
from church
join country on church.church_countryid=country_id
join state on church.church_stateid=state.state_id
join city on church.church_cityid=city.city_id
join church_mass_timing on church.church_id=church_mass_timing.church_id
join timing on church_mass_timing.time_id=timing.time_id
join language on church_mass_timing.language_id=language.language_id
join denomination on church.church_denominationid=denomination.denomination_id
order by church.church_name,
timing.time';
my $sth = $db->prepare($sql) or die $db->errstr;
$sth->execute or die $sth->errstr;
template 'church_list.tt' => { 'title' => 'church list',
'site' => 'Church Timings',
'entries' => $sth->fetchall_hashref('Time'),
};
};
this is the church_list.tt
<% INCLUDE header.tt %>
<ul id="content">
<button id="btnExport">Export to xls</button>
<br />
<br />
<div id="table_wrapper">
<% IF entries.size %>
<table border="1" cellspacing="2" widht="100%">
<tr>
<th><center>Time</center></th>
<th><center>Church name</center></th>
<th><center>Address</center></th>
<th><center>Language</center></th>
<th><center>Denomination</center></th>
<th><center>City</center></th>
<th><center>State</center></th>
<th><center>Country</center></th>
</tr>
<% FOREACH id IN entries.keys %>
<tr>
<td><% entries.$id.Time %></td>
<td><% entries.$id.Church_Name %></td>
<td><% entries.$id.Address %></td>
<td><% entries.$id.language %></td>
<td><% entries.$id.denomination %></td>
<td><% entries.$id.city %></td>
<td><% entries.$id.state %></td>
<td><% entries.$id.country %></td>
</tr>
<% END %>
</table>
</div>
<% ELSE %>
<li><em>No entries here so far.</em>
<% END %>
</ul>
Thanks.
Please take a note that fetchall_hashref returns hashref { FieldValue => FieldsHash }.
When some field values aren't unique, you missed some FieldHashes (every next record with same Time value will overwrite previous in result hashref).
Please use arrays of rows (selectall_arrayref
) in these cases.
P.S.: Time in this query always is not unique (sometimes two records may be added simultaneously).