Zend Framework

Paginator DBselect adapter not propperly counting rows when using DISTINCT()

Details

  • Type: Improvement Improvement
  • Status: Resolved Resolved
  • Priority: Major Major
  • Resolution: Fixed
  • Affects Version/s: 1.6.0RC2
  • Fix Version/s: 1.6.1
  • Component/s: Zend_Paginator
  • Labels:
    None

Description

When using the Zend_Paginator_Adapter_DBselect() on a select statement as such;

$select = $this->db->select()
->from("IPLog", "DISTINCT(IP)");

or

$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.

//update;

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.

Activity

Ascending order - Click to sort in descending order
Hide
Permalink
Jurrien Stutterheim added a comment -

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.

Show
Jurrien Stutterheim added a comment - 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.
Hide
Permalink
Jurrien Stutterheim added a comment -

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?

Show
Jurrien Stutterheim added a comment - 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?
Hide
Permalink
Jurrien Stutterheim added a comment -

Resolved in revision 10981

Show
Jurrien Stutterheim added a comment - Resolved in revision 10981
Hide
Permalink
Regis Leroy added a comment -

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_suivi_fiche` ON `ce_suivi_fiche`.ce_fiche_id = `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`.`ce_fiche_id` AS `Id`,
`ce_fiche`.`ce_fiche_foo` AS `foo`,
`ce_fiche`.`ce_fiche_bar` AS `Bar`,
FROM `ce_fiche`
LEFT JOIN `ce_suivi_fiche` ON `ce_suivi_fiche`.ce_fiche_id = `ce_fiche`.ce_fiche_id
ORDER BY 1 ASC LIMIT 10;

weel, why removing table names?

Show
Regis Leroy added a comment - 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_suivi_fiche` ON `ce_suivi_fiche`.ce_fiche_id = `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`.`ce_fiche_id` AS `Id`, `ce_fiche`.`ce_fiche_foo` AS `foo`, `ce_fiche`.`ce_fiche_bar` AS `Bar`, FROM `ce_fiche` LEFT JOIN `ce_suivi_fiche` ON `ce_suivi_fiche`.ce_fiche_id = `ce_fiche`.ce_fiche_id ORDER BY 1 ASC LIMIT 10; weel, why removing table names?
Hide
Permalink
GUYARD Vincent added a comment -

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());

Show
GUYARD Vincent added a comment - 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());

People

Vote (0)
Watch (0)

Dates

  • Created:
    Updated:
    Resolved:
Atlassian JIRA (v4.4.3#663-r165197) | Report a problem
Powered by a free Atlassian JIRA open source license for Zend Framework. Try JIRA - bug tracking software for your team.