mysqlperltemplate-toolkitdancer

template toolkit displays unique row


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.


Solution

  • 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).