Skip to content

HTTPS clone URL

Subversion checkout URL

You can clone with
or
.
Download ZIP

Loading…

Paginator\DBSelect and PostgreSQL #2656

Closed
timokhin opened this Issue · 8 comments

7 participants

@timokhin

In my AbstractTableGateway model i do this:

public function fetchAll($currentPage = 1, $countPerPage = 20) {
        $select = new Select();
        $select->from($this->table)->order('date DESC');
        $adapter = new \Zend\Paginator\Adapter\DbSelect($select, $this->adapter, $this->resultSetPrototype);
        $paginator = new \Zend\Paginator\Paginator($adapter);        
        $paginator->setItemCountPerPage($countPerPage);
        $paginator->setCurrentPageNumber($currentPage);
        return $paginator;
} 

Paginator generates sql query:

SELECT COUNT(1) AS c FROM news ORDER BY id DESC

In mysql db its okay, but Postgres requires GROUP BY section in query and returns error.

Is this issue in paginator?

For me i'm fixed it by adding

$select->reset(Select::ORDER);  // in DbSelect.php (count method)

and

case self::ORDER:
$this->order = null;
break;  // in Select.php (reset method)

It cleans select object from ORDER section and all works fine.

@ralphschindler
Collaborator

I think this is a 2 part problem. Yes, I think Select should have some way of resetting the order part of the query.

But then I am unsure the root cause of your postgres problem. I think that is a Paginator problem. Are you suggesting we remove the order as well from in here: https://github.com/zendframework/zf2/blob/master/library/Zend/Paginator/Adapter/DbSelect.php ?

@pozs

Not only ORDER BY triggers this bug, but join(s) where you have selected column(s) from the joined table(s)

SELECT COUNT(1) AS c, t.c2 FROM news JOIN t ...

When i removed join(s) and re-add all of them with empty columns, paginator worked fine - (it needs to be re-add, because some (inner) joins could modify the count)

@rumeau

I tried to create a paginator using MySQL, and it is not Postgres related only, take a query where you want results join with a count on another table, since you also need to group the paginator fails as the GROUP affects the Paginator COUNT(1) also.

SELECT COUNT(1) AS `c` FROM `categories`;

Works fine, now if i also want the number of items that belongs to a category.

SELECT COUNT(1) AS `c`, COUNT(`items`.`id`) as items FROM `categories` LEFT JOIN `items` ON `items`.`category` = `categories`.`id` GROUP BY `categories`.`id`

This query fails on the Paginator DbSelect because it groups by the category ID, therefore COUNT(1) doesnt count all the rows, instead return all the rows.

I think DbSelect count() should wrap the original query and then count on it.

SELECT COUNT(1) AS `c` FROM (SELECT COUNT(`items`.`id`) as items FROM `categories` LEFT JOIN `items` ON `items`.`category` = `categories`.`id` GROUP BY `categories`.`id`) as `t`
@acaciovilela

I have the same problem too when i use join() it requires GROUP BY and it fails.

or if i do my fetchAll() this way, the paginator doesn't work, it not count pages...

public function fetchAll(){
    $select = $this->getSql()->select();
    $select->join('countries', 'countries.country_id = states.country_id', array('country_name'))
        ->group(array('
            countries.country_name', 
            'states.country_id', 
            'states.state_id', 
            'states.state_name',
            'states.state_symbol',))
        ->order('state_name ASC');
    return new Paginator(
        new DbSelect(
            $select,
            $this->adapter,
            $this->resultSetPrototype)  );
}
@RWOverdijk

What's the status on this?

@ThaDafinser

I've added some days ago a patch, that the Paginator Resets the "GROUP BY" part (because it's needed for mysql).
https://github.com/zendframework/zf2/blob/master/library/Zend/Paginator/Adapter/DbSelect.php#L110

So will this cause more problems for Postgre?
Maybe then we have to implement an Adapter specific reseting...Adapter is already inside the paginator.

@ralphschindler
Collaborator

Can we check that this is an issue?

@ralphschindler
Collaborator

Inactivity.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Something went wrong with that request. Please try again.