mysql - 模板工具包显示唯一行

Question

我正在使用 Perl dancer2 编写应用程序。在 mysql 中运行 select query 显示所有记录。但在 dancer2 和模板工具包中运行的相同查询仅显示唯一记录。

例如。在 mysql 客户端中运行时获取 34 条记录。

    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;

Dancer 中使用 Template Toolkit 的相同查询返回 11 条记录。

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'),
};
};    

这是 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>

谢谢。

Answer 1

请注意fetchall_hashref返回 hashref { FieldValue => FieldsHash }。

当某些字段值不唯一时，您会遗漏一些 FieldHashes(每个具有相同时间值的下一条记录将覆盖结果哈希引用中的前一条)。

在这些情况下，请使用行数组 (selectall_arrayref)。

P.S.:这个查询中的时间总是不唯一的(有时可能会同时添加两条记录)。