Issue Type: Improvement Created: 2008-08-22T07:34:12.000+0000 Last Updated: 2011-08-17T22:04:02.000+0000 Status: Resolved Fix version(s): - 1.6.1 (15/Sep/08)
Reporter: Dae Daluz (dae) Assignee: Jurrien Stutterheim (norm2782) Tags: - Zend_Paginator
Related issues: Attachments:
When using the Zend_Paginator_Adapter_DBselect() on a select statement as such;
$select = $this->db->select() ->from("IPLog", "DISTINCT(IP)");
$select = $this->db->select() ->from("IPLog", "IP") ->DISTINCT(true);
the counting of rows goes wrong, since in my case there's 2 unique IP's in the table, but I get 43 since there's 43 rows in total. This causes my paginator controls to show more pages then there really are. The problem seems to be that the count() method of the DBselect adapter resets the columns, without checking for result limitting expressions.
Thanks updating to latest svn and using the group by did the trick, doesn't work with 1.6.0RC2 though, so any change made after that did the trick.
Posted by Jurrien Stutterheim (norm2782) on 2008-08-22T07:37:21.000+0000
Could you please try this with the latest version from the SVN? There have been a lot of improvements to the DbSelect adapter since RC2.
Posted by Jurrien Stutterheim (norm2782) on 2008-08-22T08:44:41.000+0000
I've managed to write a fix for this for the simple usecase where you would want to select only one column. Full support for multiple columns is somewhat more complicated. Please note that I've not committed the fix just yet.
An alternate solution is to use GROUP BY instead of DISTINCT, so I'm inclined to use that fact as a solution to this issue. What would be a usecase where DISTINCT is preferred over GROUP BY?
Posted by Jurrien Stutterheim (norm2782) on 2008-08-22T14:01:42.000+0000
Resolved in revision 10981
Posted by Regis Leroy (regilero) on 2008-10-23T16:27:36.000+0000
For me this is not completly fixed (tested with 1.6.1). I had to change a distinct in Group By for a query using several tables where one off the fields of the select were present in several tables of the request. I obtained something like: SELECT COUNT(DISTINCT ce_fiche_id,ce_fiche_foo,ce_fiche_bar) AS zend_paginator_row_count FROM
ce_fiche LEFT JOIN
ce_fiche.ce_fiche_id WHERE .... ==> this count query cannot work because ce_fiche_id is ambigous. Initial query was of this form (simplified, more complex in reality and getting the group by working was not so simple): SELECT DISTINCT
ce_fiche LEFT JOIN
ce_fiche.ce_fiche_id ORDER BY 1 ASC LIMIT 10;
weel, why removing table names?
Posted by GUYARD Vincent (vguyard) on 2011-08-17T22:04:02.000+0000
The bug is still here !!!
The query below is an example :
$query = parent::select(true)->setIntegrityCheck(false)->distinct(true) ->from(array('fu1'=>$this->_name), null) ->join(array('b'=>Model_Table_Blabla::$name), 'b.facebook_uid = fu1.facebook_uid', null) ->join(array('v'=>Model_Table_Vote::$name), 'v.id_blabla = b.id_blabla', null) ->join(array('fu2'=>$this->_name), 'v.facebook_uid = fu2.facebook_uid') ->where('fu1.facebook_uid = ?', $array['facebook_user']->getFacebookUid());
Have you found an issue?
See the Overview section for more details.