A bunch of improvements to the Doctrine ORM pagination.

The CountWalker and LimitSubqueryWalker have been implemented using output walkers instead of tree walkers. The WhereInWalker has been extended. Improvements:

• Support for more complex DQL queries using named mixed results with GROUP BY and HAVING. For example: SELECT g, u, COUNT(u.id) AS userCount FROM Entity\Group g LEFT JOIN g.users u GROUP BY g.id HAVING userCount > 0
• Support for WHERE NOT queries
• Better performance. The CountWalker and WhereInWalker use subselects. Most databases can cache subqueries individually, so it is possible to reuse the CountWalker result for the LimitSubqueryWalker. To prevent cache misses, the CountWalker no longer strips ORDER BY clauses.
• Support for entities with composite primary keys in the CountWalker and LimitSqlWalker. Only the WhereInWalker still needs to be updated for full composite primary key support. But someone smarter than me needs to look at that and figure out how to build a WHERE IN query that can select rows based on multiple columns.

CC: @Nico-B @pablodip @Seldaek

This PR includes all commits from #48 and fixes all points that were pending in it.

However, I figured out that the adapter is broken in the case of a join (and the expectation were wrong in the test). See the my third commit making the test fail. I will try finding a solution but maybe @mtotheikle has an idea ?

https://github.com/whiteoctober/Pagerfanta/blob/f71a529c7a2711ec830396eb2bcc11d14ab0fde8/src/Pagerfanta/Pagerfanta.php#L65

Hello! When i'm try to use psalm static analyzer, it produce an error.

Class or interface Pagerfanta\Boolean does not exist

Please can you use lowercase typehints, like in other methods. Because analyzer thinks it's PSR-4 classname (starts with capital letter).

This issue is pertinent to Pagerfanta\Adapter\DoctrineORMAdapter.

I've run into performance problems with the way that the 'count' part of the pagination query is being constructed as referenced in the following issues (across multiple projects) (using MySQL):

https://github.com/KnpLabs/knp-components/pull/77 https://github.com/doctrine/doctrine2/pull/298 https://github.com/KnpLabs/knp-components/issues/65 https://github.com/KnpLabs/KnpPaginatorBundle/issues/202 https://github.com/beberlei/DoctrineExtensions/pull/59

This issue will be relevant to anyone using the paginator with MySQL and anything approaching a large number of rows in a database (for me 80k) - when it can take > 100 seconds to execute the count part of the query on a modest amount of data.

My goal here is to simplify the poorly performing query being generated, given that I'm not performing any join or group conditions (and I appreciate why the below structure may be required if this were the case), I have no need for either the 'distinct' subquery, nor the inner subquery it selects from. I'm trying to get from this:

SELECT 
  COUNT(*) AS dctrn_count 
FROM 
  (
    SELECT 
      DISTINCT id0 
    FROM 
      (
        SELECT 
          o0_.id AS id0, 
          o0_.ordr_id AS ordr_id1, 
          ...etc
        FROM 
          ordr_status o0_
        WHERE
          some_property = 'something'
      ) dctrn_result
  ) dctrn_table

to this:

SELECT COUNT(*) AS dctrn_count
    FROM ordr_status o0_
    WHERE some_property = 'something'

or this (I don't care about the DISTINCT as it doesn't seem to hurt performance):

SELECT COUNT(DISTINCT id0) AS dctrn_count
    FROM ordr_status o0_
    WHERE some_property = 'something'

It seems the way to do this is to avoid using the 'Doctrine\ORM\Tools\Pagination\CountOutputWalker' within the Doctrine paginator.

I've tried a variety of changing query walkers, query hints, and changing options in the paginator, and I've found I can achieve what I want by doing this in the DoctrineORMAdapter constructor:

$this->paginator = new DoctrinePaginator($query, false);
$this->paginator->setUseOutputWalkers(false);

So: Could/Should the option to disable use of output walker be available in the adapter constructor:

public function __construct($query, $fetchJoinCollection = true, $useOutputWalkers = true)
{
    if (class_exists('Doctrine\ORM\Tools\Pagination\Paginator')) {
        $this->paginator = new DoctrinePaginator($query, $fetchJoinCollection);
        if (!$useOutputWalkers) {
          $this->paginator->setUseOutputWalkers(false);
        }
    } else {
        $this->paginator = new LegacyPaginator($query, $fetchJoinCollection);
    }
}

OR is there an alternative way to get this behaviour (such as setting a hint on the query before passing it to the adapter) that I've missed.

I'm receiving this exception when trying to get current page results :

Single id is not allowed on composite primary key in entity Nk\PlayerBundle\Entity\Message_Player

I need theses 2 @id I've in my Entity @ManyToOne associations to other Entities.

Can you help me ?

Thanks!

hi,

I try to use order by whit a not mapped field, like this :

Select AVG(xxx) as alias From ... Where ... Order By alias

in LimitSubqueryWalker : $item->expression->identificationVariable, $item->expression->field

this properties dont exists for an order by whit not mapped field and make an error. But when I bypass the code (just for test) I have a SQLwalker error.

Problem appear when we use fetch joined collections and not mapped field in order by, the subquery generation fail.

Hi,

Sam here, one of the current maintainers of this project.

Given that we no longer use PHP in our active projects, it's becoming harder to give this plugin the time it needs.

Therefore, we're looking for new maintainer(s) for this project.

We'd like any new prospective maintainer to fork this project, and then once that fork has moved forward enough to give us confidence in your ownership, we would link to your fork prominently from the top of the README here, encouraging others to use it instead.

If you're interested, please go ahead and fork (if you haven't already) and start work! If you've forked and would like others to help you, feel free to reply in this issue with details of your fork.

Please do reply here if you have questions. Thanks in anticipation.

The view option 'proximity' cannot be defined to 0. When defined to 0, It seems that the proximity is automatically pushed to 2. This is a really hard constraint to use pagerfanta on mobile templates...

I don't know if this is a real issue, but I think must tell you.

First, I use Pagerfanta on Symfony 2.0.X.

The best way is an example, I use a query like this:

$q = $em->createQueryBuilder();
$q->select('a, b, c');
$q->from('AcmeBundle:Aaa','a JOIN a.bbb b LEFT JOIN b.ccc c');
$q->addOrderBy('c.foo', 'ASC');

I use an "order by" in a field that is aggregate in an "left join".

This cause the result of the query (that search the id's in the getIterator() method) add the "c.foo" field to the select stament, and this miss the distinct of a.id, because now the select stament is "DISTINCT a.id, c.foo" and this must be only "DISTINCT a.id".

The problem is: id's obtained is a repetition of "a.id" for each "c.foo" distinct element, this break the count of the paginator.

I think the query that search id's will be similar to the query that get the count (except the count function), only where clause's and one field on select stament.

The order by clause is only required in the last query that get the final result (I think).

Hi

Is there any helper method to output the paginator properties in array or json format? similar to Zend Paginator toJson method with JsonSerializable support? my current workaround:

    public function toArray(Pagerfanta $pagerfanta)
    {
        $items = (array) $pagerfanta->getCurrentPageResults();

        return [
            'items'             => $items,
            'count_results'     => $pagerfanta->getNbResults(),
            'current_page'      => $pagerfanta->getCurrentPage(),
            'number_of_pages'   => $pagerfanta->getNbPages(),
            'next'              => ($pagerfanta->hasNextPage()) ? $pagerfanta->getNextPage() : null,
            'prev'              => ($pagerfanta->hasPreviousPage()) ? $pagerfanta->getPreviousPage() : null,
            'paginate'          => $pagerfanta->haveToPaginate(),
        ];
    }

The resultset in Solarium has additional methods to get things like the facetset. The visibility of getResultSet() is private which means you can't access these without doing another query.

Perhaps just change the getResultSet() visibility to public?

Sample usecase:

$pager = new Pagerfanta(new DoctrineDbalAdapter($query, function (QueryBuilder $queryBuilder) {
    return $queryBuilder->getConnection()
        ->createQueryBuilder()
        ->select('COUNT(*) AS total')
        ->from('(' . $queryBuilder->getSQL()  . ')', 'QL')
        ->setMaxResults(1)
        ->setParameters($queryBuilder->getParameters());
}));

The current MongoAdaptor is based on the Depreciated php-mongo driver, all post v 7.x drivers are based on the php-mongodb driver that has a different access api

We added this library to our api to pass in the results serialized to our UI, however, it seems like it does not work well with Symfony serializer. Any idea why? It’s cause slowness and performance issues. Seems like it’s not playing well with the serializer.

Currently the DoctrineODMMongoDBAdapter only supports the Query Builder:

• https://github.com/doctrine/mongodb-odm/blob/master/lib/Doctrine/ODM/MongoDB/Query/Builder.php

But there is also the Aggregation Builder:

• https://github.com/doctrine/mongodb-odm/blob/master/lib/Doctrine/ODM/MongoDB/Aggregation/Builder.php

Adding an additional adapter based on the existing one is actually easy:

use Doctrine\ODM\MongoDB\Aggregation\Builder;
use Pagerfanta\Adapter\AdapterInterface;

class DoctrineODMMongoDBAggregationAdapter implements AdapterInterface
{
    private $aggregationBuilder;

    /**
     * Constructor.
     *
     * @param Builder $aggregationBuilder A DoctrineMongo aggregation builder.
     */
    public function __construct(Builder $aggregationBuilder)
    {
        $this->aggregationBuilder = $aggregationBuilder;
    }

    /**
     * Returns the query builder.
     *
     * @return Builder The query builder.
     */
    public function getAggregationBuilder()
    {
        return $this->aggregationBuilder;
    }

    /**
     * {@inheritdoc}
     */
    public function getNbResults()
    {
        return $this->aggregationBuilder->execute()->count();
    }

    /**
     * {@inheritdoc}
     */
    public function getSlice($offset, $length)
    {
        return $this->aggregationBuilder
            ->limit($length)
            ->skip($offset)
            ->execute();
    }
}

Unfortunately none of the two builders implement a common Interface. But there is another reason why using a single adapter for both use cases could be problematic!

The aggregation builder uses a pipeline. Each time a so called "stage" e.g. limit oder sort is added the return value of the assigned variable changes.

$qb = $this
    ->createAggregationBuilder();
    ->sort(['leavingDate' => 1])
    ->limit($query->limit);

dump($qb); // Instance of Limit extends Stage

$qb = $this
    ->createAggregationBuilder();
$qb
    ->sort(['leavingDate' => 1])
    ->limit($query->limit);

dump($qb); // Instance of Builder

As soon as you pass a Stage instance you can not retrieve the original builder:

• https://github.com/doctrine/mongodb/blob/master/lib/Doctrine/MongoDB/Aggregation/Stage.php#L19

Also there are plans for ODM version 2 which may change this behaviour:

• https://github.com/doctrine/mongodb-odm/pull/1856

Maybe @alcaeus or @malarzm could comment on this too? Thanks in advance!

Possibly related issues:

• https://github.com/doctrine/mongodb-odm/issues/1988#issuecomment-478009400 @ychanan

Problem

• There was currently no way to have offset for pagerfanta :disappointed:

Solution

• Offset is now working and I also added simple test :smile:

public function getAuthorProducts($uuid)
{
    $queryBuilder = $this->createQueryBuilder('o')
        ->addSelect('author')
        ->innerJoin('o.author', 'author')
        ->andWhere("author.uuid=:uuid")
        ->andWhere("o.enabled=:enabled")
        ->andWhere("o.isShow=:isShow")
        ->setParameter("isShow", true)
        ->setParameter("enabled", true)
        ->setParameter("uuid", $uuid)
        ->setFirstResult(5)
    ;

    return $this->getPaginator($queryBuilder);
}

I print the Paginator

Query Builder has FirstResult in it

But FirstResult didn't work in the end

I notice that "Pagerfanta/Pagerfanta, "line 316 is not going to fetch the initial FirstResult

private function getCurrentPageResultsFromAdapter()
{
    $offset = $this->calculateOffsetForCurrentPageResults();
    $length = $this->getMaxPerPage();

    return $this->adapter->getSlice($offset, $length);
}

private function calculateOffsetForCurrentPageResults()
{
    return ($this->getCurrentPage() - 1) * $this->getMaxPerPage();
}