Databases
StackOverflow2013
Postsdbo
PONot getting distinct query results in Symfony 1.4 / Doctrine
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PONot getting distinct query results in Symfony 1.4 / Doctrine
    text
    copied!<p>I've got this query in my TurnoveroverVolumeTable class:</p> <pre><code>$query = $this-&gt;createQuery('tv') -&gt;addSelect('DISTINCT tv.debtor_number') -&gt;addSelect('tv.debtor_name') -&gt;addSelect('tv.country_description') -&gt;addSelect('SUM(tv.turnover_ytd) as tv.turnover_ytd') -&gt;addSelect('tv.currency_description') -&gt;from('TurnoverVolume tv') -&gt;innerJoin('tv.Agent a') -&gt;andWhere('tv.debtor_number = a.debtor_number') -&gt;andWhere('a.agent_number =?', $agent) -&gt;andWhere('tv.period LIKE "' . $year .'%"') -&gt;groupBy('tv.debtor_number'); </code></pre> <p>When retrieving and displaying the data I use this in my actions class:</p> <pre><code>$query = Doctrine_Core::getTable('TurnoverVolume')-&gt;getYearTurnover($agent_number, $this-&gt;currentYear); $results = $query-&gt;execute(); $turnover_data = array(); foreach ($results as $turnover) { $turnover_result = array( 'debtor_number' =&gt; $turnover['debtor_number'], 'debtor_name' =&gt; $turnover['debtor_name'], 'country_description' =&gt; $turnover['country_description'], 'turnover_ytd' =&gt; $turnover['turnover_ytd'], 'currency_description' =&gt; $turnover['currency_description'] ); $turnover_data[] = $turnover_result; } </code></pre> <p>The result I'm getting for turnover_ytd is 2703 but should be 277450, it only gets the first result of the year 2011 and not the total result of the year 2011.</p> <p>In the logs of Symfony I have this MySQL query:</p> <pre><code>SELECT DISTINCT t.company_number AS t__company_number, t.debtor_number AS t__debtor_number, t.period AS t__period, t.debtor_name AS t__debtor_name, t.country_description AS t__country_description, t.currency_description AS t__currency_description, SUM(t.turnover_ytd) AS t__0 FROM turnover_volume t INNER JOIN agent a ON t.debtor_number = a.debtor_number WHERE (t.debtor_number = a.debtor_number AND a.agent_number = '003' AND t.period LIKE "2011%") GROUP BY t.debtor_number </code></pre> <p>Which gets me the right result, when I enter it directly into the MySQL database.</p> <p>Can anyone expain to me what I might be doing wrong?</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload