ZF-7299: Zend_Paginator wrong query when getting number of results

Description

Hi there,

I have problem after Zend_Paginator tries to get the number of results.

Here is the example:


$users = new Users();
$rows = $users->fetchAll($select);

SELECT
`users`.`id`,
`users`.`first_name`,
`users`.`last_name`,
`users`.`position`,
`users`.`username`,
`users`.`email`,
(
    SELECT time 
    FROM events 
    WHERE
        events.target_id = users.id
        AND
        action = 'eventLogin'
        AND
        target_table = 'users'
    ORDER BY time DESC
    LIMIT 1
) AS `last_login_date`, 
(
    SELECT time 
    FROM events 
    WHERE
        events.target_id = users.id 
        AND 
        action = 'eventCreate' 
        AND 
        target_table = 'users' 
    ORDER BY time ASC 
    LIMIT 1
) AS `creation_date`, 
(
    SELECT COUNT(id) 
    FROM events 
    WHERE 
        events.target_id = users.id 
        AND 
        action = 'eventAccessCodesSent' 
        AND 
        target_table = 'users'
) AS `access_codes_sent`, 
IF (users.status = '1', 1, 0) AS `active`,
`roles_translations`.`name` AS `role_name`,
`languages_translations`.`name` AS `language_name`,
GROUP_CONCAT(DISTINCT countries_translations.name SEPARATOR ', ') AS `countries`,
`groups`.`name` AS `groups`,
GROUP_CONCAT(DISTINCT branches_translations.name SEPARATOR ', ') AS `branches`,
`media`.`name` AS `media_name`,
`types_translations`.`title` AS `media_type`,
`coverage_translations`.`title` AS `media_coverage`,
GROUP_CONCAT(DISTINCT categories_translations.name SEPARATOR ', ') AS `categories` 

FROM `users` 

INNER JOIN `roles` 
    ON users.role_id = roles.id AND roles.type != 7 
LEFT JOIN `roles_translations` 
    ON users.role_id = roles_translations.role_id 
    AND roles_translations.language_id = 1 
LEFT JOIN `languages_translations` 
    ON users.language_id = languages_translations.id 
    AND languages_translations.language_id = 1 
LEFT JOIN `users_countries` 
    ON users.id = users_countries.country_id 
LEFT JOIN `countries_translations` 
    ON users_countries.country_id = countries_translations.country_id 
    AND countries_translations.language_id = 1 
LEFT JOIN `users_groups` 
    ON users.id = users_groups.user_id 
LEFT JOIN `groups` 
    ON users_groups.group_id = groups.id 
LEFT JOIN `users_branches` 
    ON users.id = users_branches.user_id 
LEFT JOIN `branches_translations` 
    ON users_branches.branch_id = branches_translations.branch_id 
    AND branches_translations.language_id = 1 
LEFT JOIN `users_media` 
    ON users.id = users_media.user_id 
LEFT JOIN `media` 
    ON users_media.media_id = media.id 
LEFT JOIN `types_translations` 
    ON media.type_id = types_translations.type_id 
    AND types_translations.language_id = 1 
LEFT JOIN `coverage_translations` 
    ON media.coverage_id = coverage_translations.coverage_id 
    AND coverage_translations.language_id = 1 
LEFT JOIN `users_categories` 
    ON users.id = users_categories.user_id 
LEFT JOIN `categories_translations` 
    ON users_categories.category_id = categories_translations.category_id 
    AND categories_translations.language_id = 1 

WHERE (users.status != 2) 

GROUP BY `users`.`id` 

HAVING (creation_date >= '2009-07-17')

$paginator = new Dnez_Paginator(
new Zend_Paginator_Adapter_DbTableSelect($select)
);

$paginator->setLimit(20, 0);
$paginator->setItemCountPerPage(20);
$paginator->setPageRange(5);

SELECT
COUNT(1) AS `zend_paginator_row_count`

FROM (

    SELECT
    `users`.`id`,
    `users`.`first_name`,
    `users`.`last_name`, 
    `users`.`position`, 
    `users`.`username`, 
    `users`.`email`, 
    (
        SELECT time 
        FROM events 
        WHERE 
            events.target_id = users.id 
            AND 
            action = 'eventLogin' 
            AND 
            target_table = 'users' 
        ORDER BY time DESC 
        LIMIT 1
    ) AS `last_login_date`, 
    (
        SELECT time 
        FROM events 
        WHERE 
            events.target_id = users.id 
            AND 
            action = 'eventCreate' 
            AND target_table = 'users' 
        ORDER BY time ASC 
        LIMIT 1
    ) AS `creation_date`, 
    (
        SELECT COUNT(id) 
        FROM events 
        WHERE 
            events.target_id = users.id 
            AND 
            action = 'eventAccessCodesSent' 
            AND 
            target_table = 'users'
    ) AS `access_codes_sent`,
    IF (users.status = '1', 1, 0) AS `active`,
    `roles_translations`.`name` AS `role_name`,
    `languages_translations`.`name` AS `language_name`,
    GROUP_CONCAT(DISTINCT countries_translations.name SEPARATOR ', ') AS `countries`,
    `groups`.`name` AS `groups`,
    GROUP_CONCAT(DISTINCT branches_translations.name SEPARATOR ', ') AS `branches`,
    `media`.`name` AS `media_name`,
    `types_translations`.`title` AS `media_type`,
    `coverage_translations`.`title` AS `media_coverage`, 
    GROUP_CONCAT(DISTINCT categories_translations.name SEPARATOR ', ') AS `categories` 
    
    FROM `users`
    
    INNER JOIN `roles` 
        ON users.role_id = roles.id 
        AND roles.type != 7
    LEFT JOIN `roles_translations` 
        ON users.role_id = roles_translations.role_id 
        AND roles_translations.language_id = 1
    LEFT JOIN `languages_translations` 
        ON users.language_id = languages_translations.id 
        AND languages_translations.language_id = 1
    LEFT JOIN `users_countries` 
        ON users.id = users_countries.country_id
    LEFT JOIN `countries_translations` 
        ON users_countries.country_id = countries_translations.country_id 
        AND countries_translations.language_id = 1
    LEFT JOIN `users_groups` 
        ON users.id = users_groups.user_id
    LEFT JOIN `groups` 
        ON users_groups.group_id = groups.id
    LEFT JOIN `users_branches` 
        ON users.id = users_branches.user_id
    LEFT JOIN `branches_translations` 
        ON users_branches.branch_id = branches_translations.branch_id 
        AND branches_translations.language_id = 1
    LEFT JOIN `users_media` 
        ON users.id = users_media.user_id
    LEFT JOIN `media`
        ON users_media.media_id = media.id
    LEFT JOIN `types_translations` 
        ON media.type_id = types_translations.type_id 
        AND types_translations.language_id = 1 
    LEFT JOIN `coverage_translations` 
        ON media.coverage_id = coverage_translations.coverage_id 
        AND coverage_translations.language_id = 1
    LEFT JOIN `users_categories` 
        ON users.id = users_categories.user_id
    LEFT JOIN `categories_translations` 
        ON users_categories.category_id = categories_translations.category_id 
        AND categories_translations.language_id = 1
                 
    WHERE (users.status != 2) 
    
    GROUP BY `users`.`id` 
    
    HAVING (creation_date >= '2009-07-17')

) AS `t` 

-- the problematic where
WHERE (users.status != 2)

Comments

Could you confirm that this error still exists with the latest trunk?

I think this code example below fixes the bug:

            
// the first row below this comment is nuber 250 of Zend/Paginator/Adapter/DbSelect.php file

$rowCount->reset(Zend_Db_Select::COLUMNS)
    ->reset(Zend_Db_Select::ORDER)
    ->reset(Zend_Db_Select::LIMIT_OFFSET)
    ->reset(Zend_Db_Select::GROUP)
    ->reset(Zend_Db_Select::DISTINCT)
    ->reset(Zend_Db_Select::HAVING)
    ->reset(Zend_Db_Select::WHERE) //the missing reset call fixing this bug
    ->columns($expression);

Hi again,

Yes i think the problem is still there. The where clause is not reset.

Can you confirm that? The where clause does not need to be reset there in most cases, so please double check :)

Yes. The where clause in count query is not needed. You can see from example above. The count query looks like this:



SELECT
    COUNT(1)
FROM
(
/* here is the original query */
)

WHERE
/* where clause from original query  - causes SQL error */

In example above the where clause is not needed but only in count query. In current version we have the same where clauses in count and original queries which causes the SQL error because the count query don't have columns from where clause of original query. So the where clause in count query is wrong and never needed.

I made small correction of the code. So the clearing must be applied here:


/** 
* If there is more than one column AND it's a DISTINCT query, more
* than one group, or if the query has a HAVING clause, then take
* the original query and use it as a subquery os the COUNT query.
*/
if (($isDistinct && count($columnParts) > 1) || count($groupParts) > 1 || !empty($havingParts)) {
    $rowCount->reset(Zend_Db_Select::FROM);
    $rowCount->reset(Zend_Db_Select::WHERE); //clear and where clause
    $rowCount->from($this->_select);
} else if ($isDistinct) {

In current trunk the count query works. New code:


Old code:

$rowCount->reset(Zend_Db_Select::FROM); $rowCount->from($this->_select); ```