I'm getting this bug on line 123:

$result = $whereInQuery->execute();

When trying to paginate, if i do not force wrap-queries to true,
i've got this error :

Invalid parameter number: number of bound variables does not match number of tokens

I can send you the full report if needed.

here is the code that trigger the error :

$paginator = $this->container->get('knp_paginator');
$pagination = $paginator->paginate(
    $query,
    $request->query->get('page', 1),
    $this->container->getParameter('nb_sub_account_per_page')           
);

and the working code :

$paginator = $this->container->get('knp_paginator');
$pagination = $paginator->paginate(
    $query,
    $request->query->get('page', 1),
    $this->container->getParameter('nb_sub_account_per_page'),  
    array('wrap-queries'=>true)         
);

it seems that in my case the Doctrine\ORM\Tools\Pagination\CountOutputWalker works but not the Doctrine\ORM\Tools\Pagination\CountWalker

any idea ?

I have to paginate a Doctrine query with sub queries doing aggregates. I have to paginate over 1000 rows.

My orignal query looks like :

SELECT 
   r, u, d, c.value AS value_c, 
   (SELECT COUNT(a.id) FROM Model:EntityA a WHERE ...) AS count_a, 
   (SELECT SUM(b.number) FROM Model:EntityB b) AS sum_b, ...
FROM Model:EntityR r
LEFT JOIN Model:EntityC c,
LEFT JOIN Model:EntityR r,
LEFT JOIN Model:EntityU u
WHERE ...

I have 5 aggregates like this.

1. Paginator do a select count (original query)
2. Paginator do a select distinct id ... with limit/offset
3. Paginator run original with a where in clause

1 is very slow because it run the query and aggregates on all 1000 rows. I solved it by using the knp_paginator.count hint to do my own count query.

2 souldn't be slow because it select only id from the original query. Nevertheless, it's not the case. All the fields of the selected entities are removed from the original query but the aliased field and subquery are kept. So paginator run a query like :

SELECT DISTINCT r.id, c.value AS value_c (SELECT COUNT(a.id) FROM ...) AS count_a, (SELECT SUM(b.number) FROM ...)  FROM ...

Instead of :

SELECT DISTINCT r.id FROM ...

It as for consequence to run aggregates sub query on all the 1000 rows which is very slow.

The problem seems to be in the Knp\Component\Pager\Event\Subscriber\Paginate\Doctrine\ORM\QuerySubscriber where custom tree walkers are added.

Why my subquery aren't removed from the original query ? Is it a way to inject a custom query result as the knp_paginator.count hint ?

After this doctrine2 commit paginator isn't working correctly and i'm getting following error:
( ! ) Fatal error: Call to undefined method Doctrine\ORM\Query\AST\PathExpression::isSimpleArithmeticExpression() in /vendor/doctrine/lib/Doctrine/ORM/Query/SqlWalker.php on line 1942. It is already mentioned in commits discussion.

Please take a look at line 30 in this file:

https://github.com/KnpLabs/knp-components/blob/master/src/Knp/Component/Pager/Event/Subscriber/Paginate/Doctrine/DBALQueryBuilderSubscriber.php#L30

"ololoshke_trololoshke" is appended to the end of the query. This was popping up in our slow query logs and looked like a potential security vulnerability in our app until we traced it down to knp-components. Quite scary!

Why is this here - debugging material accidentally left in?
Please consider removing it for the sanity of sysadmins 👍

see the jungle here: https://github.com/KnpLabs/knp-components/blob/master/src/Knp/Component/Pager/Event/Subscriber/Paginate/Doctrine/ORM/QuerySubscriber.php#L24
Oh my god.

Let's take advantage of our decoupled, event driven architecture and create one listener per version, for god sake :) !

Or use composition if we want to keep runtime check of all these versions, and still have comprehensive, readable, maintenable classes.

Related: #79, #77, ...

sorry for my bad english.
When I request a page greather than last page there are no results.
It is possible to do so if the page is greater than last page it redirects to the last available page or throw an exception?

This annotation is producing import errors when the AbstractPagination class is used. Right now it is cropping up while using FOSRestBundle to produce json results.

I experienced a memory issue with the 1.2 version of the paginator component.
My table has ~ 100 000 objects.

I isolated the commit and its this one : c3dd1ae

If I force the $treeWalker = 'Doctrine\ORM\Tools\Pagination\CountWalker';, no problem, but with the $treeWalker = 'Doctrine\ORM\Tools\Pagination\CountOutputWalker'; I have the problem.

My version of Doctrine is 2.3.3-dev, but the 2.3.4-dev is failing too.

Thanks

Everything worked just fine before updating.

"One of listeners must count and slice given target"

Stack Trace
in /var/www/vps23.peshi.se/vendor/knplabs/knp-components/src/Knp/Component/Pager/Paginator.php at line 100

@stloyd can you please tag the current version as stable? dev-master seems to have disappeared from packagist so now I cannot include the last bugfix into my project using composer anymore...

$ ../composer.phar update
Loading composer repositories with package information
Updating dependencies (including require-dev)                                          
Your requirements could not be resolved to an installable set of packages.

  Problem 1
    - The requested package knplabs/knp-components dev-master could not be found.

Potential causes:
 - A typo in the package name
 - The package is not available in a stable-enough version according to your minimum-stability setting
   see <https://groups.google.com/d/topic/composer-dev/_g3ASeIFlrc/discussion> for more details.

Read <http://getcomposer.org/doc/articles/troubleshooting.md> for further common problems.

Please replace line no 17 for file

• src/Knp/Component/Pager/Event/Subscriber/Paginate/SolariumQuerySubscriber.php
• src/Knp/Component/Pager/Event/Subscriber/Sortable/SolariumQuerySubscriber.php

because new solarium bundle class structure is following psr starndards.

if (is_array($event->target) && 2 === count($event->target) && reset($event->target) instanceof \Solarium\Client && end($event->target) instanceof \Solarium\QueryType\Select\Query\Query) { 

Use Case

Our system is paginating a Doctrine Query object with the KnpPaginator (via the Knp PaginatorBundle) in order to execute a batch-processing job.
KnpPaginator is the preferred paginator system due to its ability to abstract and deal with multiple data sources for pagination.

The batch processing system calls the ->paginate() method repeatedly (~140 times) to fetch batches of results for processing. Yes, there's ways we can do this more efficiently, but we're slowly working on the Paginator to make these scenarios faster while still maintaining the desired level of abstraction.

High-Level Problem

Repeated calls the pagination service lead to excessive memory usage.
The paginator slowly becomes unresponsive with each iteration of the ->paginate() call.

Factors

• Symfony 2.3
• PHP 5.6.27 with OPcache
• Using Monolog NullHandler attached to TraceableEventDispatcher, to eliminate memory leak during logging of the EventDispatcher system.
• Paginate item reduced to a basic array, as opposed to a query.

Example Code

$paginator = $container->get('knp_paginator');
for ($i=0; $i<1000; $i++) {
    $pagination = $paginator->paginate([1,2,3,4,5,6, 7, 8, 9, 10], $i, 10);
}

Low-Level Problem

\Knp\Component\Pager\Paginator maintains its own eventDispatcher for its entire life, and is never reset or replaced. In Symfony this is the Kernel EventDispatcher

The eventDispatcher is populated with these basic subscribers:
\Knp\Component\Pager\Event\Subscriber\Paginate\PaginationSubscriber
\Knp\Component\Pager\Event\Subscriber\Sortable\SortableSubscriber
Either during Construct or as part of the service definition in the KnpPaginatorBundle.

The Paginator can also be added to using the subscribe() and connect() methods, which indicates that the Paginator and EventDispatch is supposed to act as a single-instance service for the lifetime of the application.

\Knp\Component\Pager\Paginator#paginate() has a BeforeEvent (knp_pager.before) which is used to prime the paginator to act upon paginatable items.
This is created as follows:
https://github.com/KnpLabs/knp-components/blob/master/src/Knp/Component/Pager/Paginator.php#L107

$beforeEvent = new Event\BeforeEvent($this->eventDispatcher);

\Knp\Component\Pager\Event\Subscriber\Paginate\PaginationSubscriber#before is subsequently called, which is used to attach (lazy-load) the standard paginatable subscribers:

    public function before(BeforeEvent $event)
    {
        $disp = $event->getEventDispatcher();
        // hook all standard subscribers
        $disp->addSubscriber(new ArraySubscriber);
        $disp->addSubscriber(new Doctrine\ORM\QueryBuilderSubscriber);
        // ... etc ...
    }

These calls to addSubscriber are actually being affected on the eventDispatcher attach to the persistent Paginator class (in Symfony, this is the Kernel Event Dispatcher).

Effectively, each time we're calling ->paginate() the knp_pager.before event is being called, which attached more subscribers to the Kernel Event Dispatcher. This eventually results in a significant memory leak.

Subscribers that exhibit this behaviour:

\Knp\Component\Pager\Event\Subscriber\Paginate\PaginationSubscriber
\Knp\Component\Pager\Event\Subscriber\Filtration\FiltrationSubscriber
\Knp\Component\Pager\Event\Subscriber\Sortable\SortableSubscriber

Intended use?

\Knp\Component\Pager\Event\BeforeEvent looks like its was designed purely to perform this lazy-loading Subscriber registration action, as it only provides scope over the EventDispatcher and nothing else.

The fact that BeforeEvent can be called multiple times seems like an oversight in the design of the ->paginate() method.

Proposed fix

The 3 subscriber-loading-subscribers which cause this problem in the first place are merely acting as lazy-loaders.
These 3 classes can be augmented to hold state, which will prevent them from executing more than once on any single EventDispatcher.
This keeps the lazy-loading aspect firmly sioled to the domain of the Subscriber, and lets the Paginator not care about how Subscribers are loaded.
Implementors with similar subscribers will have to add this behaviour to their own subscribers in order to prevent the duplication and subsequent memory leak bug.

What now

I'll be submitting a PR shortly (~1 day) to provide the fix proposed above.
If there is issue with this solution (and I looked at ~5) please let me know so I can amend the fix appropriately.

I am working on a pimple/silex integration https://github.com/saxulum/saxulum-pagination-provider, if you whish we could add it to knplabs with your namespaces.

I have entity with composite key:

class ProductBookmark
{
    /**
     * @ORM\Id
     * @ORM\ManyToOne(targetEntity="User")
     */
    private $user;

    /**
     * @ORM\Id
     * @ORM\ManyToOne(targetEntity="Product")
     */
    private $product;
    ...
}

And I try to paginate this query:

$query = $repository
    ->createQueryBuilder('pb')
    ->select('pb')
    ->andWhere('pb.user = :user')
    ->setParameter('user', $user)
    ->getQuery();

But runnable query is:

SELECT COUNT(*) AS dctrn_count FROM 
  (SELECT DISTINCT user_id0, product_id1 FROM 
    (SELECT p0_.user_id AS user_id0, p0_.product_id AS product_id1 
     FROM product_bookmarks p0_ WHERE p0_.user_id = 'Object(Test\\TestBundle\\Entity\\User)'
  ) dctrn_result
) dctrn_table

p.s. using knp-components 1.2.2

I'm not really sure whether this is a doctrine issue or knp, but this could be fixed by not using Doctrine\ORM\Tools\Pagination\CountOutputWalker.

When Doctrine CountOutputWalker is used the generated SQL looks like this:
SELECT COUNT(*) AS dctrn_count FROM (SELECT DISTINCT id6 FROM (SELECT q0_.subject AS subject0, q0_.views AS views1, q0_.content AS content2, q0_.active AS active3, q0_.created_at AS created_at4, q0_.updated_at AS updated_at5, q0_.id AS id6, q0_.user_id AS user_id7, q0_.category_id AS category_id8 FROM question q0_ ORDER BY q0_.id DESC) dctrn_result) dctrn_table

mysql> SELECT COUNT(*) AS dctrn_count FROM (SELECT DISTINCT id6 FROM (SELECT q0_.subject AS subject0, q0_.views AS views1, q0_.content AS content2, q0_.active AS active3, q0_.created_at AS created_at4, q0_.updated_at AS updated_at5, q0_.id AS id6, q0_.user_id AS user_id7, q0_.category_id AS category_id8 FROM question q0_ ORDER BY q0_.id DESC) dctrn_result) dctrn_table;
+-------------+
| dctrn_count |
+-------------+
| 59328 |
+-------------+
1 row in set (0.24 sec)

and when it's not used:
mysql> SELECT count(DISTINCT q0_.id) AS sclr0 FROM question q0_;
+-------+
| sclr0 |
+-------+
| 59328 |
+-------+
1 row in set (0.02 sec)

Fatal error: Call to a member function getName() on a non-object in /var/www/site.com/vendor/knp-components/src/Knp/Component/Pager/Event/Subscriber/Paginate/Doctrine/ORM/QuerySubscriber.php on line 94

code to reproduce:

<?php

$query = $em = $this->getDoctrine()->getEntityManager()
            ->createQuery('SELECT m FROM MyBundle:MyEntity m')
            ->useResultCache(true)
            ->setResultCacheLifetime($seconds = 3600)
            ->setResultCacheId('model_profiles')
        ;
$paginator = $this->get('knp_paginator');
$pagination = $paginator->paginate(
    $query,
    $this->get('request')->query->get('page', 1),
    10
);

Setting useResultCache(false) stops the error. Notably, defining or not defining doctrine caches in config.yml makes no difference.

class PaginationSubscriber [1] defines subscribers to execute pagination
however the order of subscriber is broken, as code in DBALQueryBuilderSubscriber will never be executed, because it expect target to be instanceof QueryBuilder.
this will never happen because QueryBuilderSubscriber is executed sooner and it transform QueryBuilder into Query

DBALQueryBuilderSubscriber should be removed as it doesn't serve any purpose and there are no tests referring or testing this subscriber.

[1]
https://github.com/KnpLabs/knp-components/blob/756c44523db9cf125381cba2cb08160fd184fb24/src/Knp/Component/Pager/Event/Subscriber/Paginate/PaginationSubscriber.php

Hello guys,

Is there a way to get array of entity objects instead of array of arrays in paginator object? I need entity objects to correct usage of custom Voter + isGranted.

Thank you

PHP 5.3.3 automated build check fails. This means all new pull requests checks fails. Please fix or allow ignore on error. Problem is in composer error:

`[Composer\Exception\NoSslException]

The openssl extension is required for SSL/TLS protection but is not availab

le. If you can not enable the openssl extension, you can disable this error

, at your own risk, by setting the 'disable-tls' option to true.`

The subscriber to handle Elastica Query objects does not seem to be working properly.

Trying the following simply picks up the passed in array as a plain array and tries to paginate it - obviously not working.

$paginator->paginate(
    array(
        $elasticsearchIndex,
        $elasticaQuery,
    ),
    $page,
    $limit
);

After poking around, I simply changed the ElasticaQuerySubscriber's test from using Elastica_Searchable and Elastica_Query to Elastica\SearchableInterface and Elastica\Query respectively. That seems to be working perfectly.

Is this correct? Did I do something wrong? Or is this a bug?

Will submit a pull request upon validation.

After post here about count problem when do group by, they say the real problem is here with Knp\Component\Pager\Paginator and I want to know if this will be fixed.

I want to use Propel 1.6 with KnpPaginator. Is this support slated for development in the near future / if at all?

Propel has native paging via $collection = EntityQuery::create()->paginate($this->get('request')->query->get('page', 1), $maxPerPage = 5)->getResults(), but I'd prefer to use KnpPaginator.

Thanks =)

For example in this sample code:

    $pagination = $this->paginator->paginate($query, $pageNum, 20);
    return $this->render('index.html.twig', ['pagination' => $pagination]);

The phpdoc return value of the paginate method has a type of Knp\Component\Pager\Pagination\PaginationInterface, which defines only setters. In a strict reading, we have defined a write-only black box, but the view must read from this object for it to be of use. Obviously this is PHP so we can still access things without a bunch of type errors, but this makes the API a little harder to understand.

Should there be another interface that extends PaginationInterface and \Traversable, and also defines getCurrentPageNumber(), getItemNumberPerPage(), and so on?

Hi everyone, i've forked your project and cloned, but there are sone errors while running tests.
Here is the output:

phpunit --bootstrap tests/bootstrap.php --configuration tests/phpunit.xml.dist 
PHPUnit 3.6.10 by Sebastian Bergmann.

Configuration read from /home/bigfoot/Progetti/knp-components/tests/phpunit.xml.dist

.................SSSS....F.....SSSSSE..E..E.

Time: 2 seconds, Memory: 17.50Mb

There were 3 errors:

1) Test\Pager\Subscriber\Sortable\Doctrine\ORM\QueryTest::shouldSortSimpleDoctrineQuery
Array to string conversion

/home/bigfoot/Progetti/knp-components/tests/Test/Tool/QueryAnalyzer.php:197
/home/bigfoot/Progetti/knp-components/tests/Test/Tool/QueryAnalyzer.php:72
/home/bigfoot/Progetti/knp-components/vendor/doctrine-dbal/lib/Doctrine/DBAL/Connection.php:631
/home/bigfoot/Progetti/knp-components/vendor/doctrine-orm/lib/Doctrine/ORM/Query/Exec/SingleSelectExecutor.php:46
/home/bigfoot/Progetti/knp-components/vendor/doctrine-orm/lib/Doctrine/ORM/Query.php:264
/home/bigfoot/Progetti/knp-components/vendor/doctrine-orm/lib/Doctrine/ORM/AbstractQuery.php:737
/home/bigfoot/Progetti/knp-components/src/Knp/Component/Pager/Event/Subscriber/Paginate/Doctrine/ORM/QuerySubscriber.php:122
/home/bigfoot/Progetti/knp-components/vendor/Symfony/Component/EventDispatcher/EventDispatcher.php:148
/home/bigfoot/Progetti/knp-components/vendor/Symfony/Component/EventDispatcher/EventDispatcher.php:49
/home/bigfoot/Progetti/knp-components/src/Knp/Component/Pager/Paginator.php:108
/home/bigfoot/Progetti/knp-components/tests/Test/Pager/Subscriber/Sortable/Doctrine/ORM/QueryTest.php:76

2) Test\Pager\Subscriber\Sortable\Doctrine\ORM\QueryTest::shouldWorkWithInitialPaginatorEventDispatcher
Array to string conversion

/home/bigfoot/Progetti/knp-components/tests/Test/Tool/QueryAnalyzer.php:197
/home/bigfoot/Progetti/knp-components/tests/Test/Tool/QueryAnalyzer.php:72
/home/bigfoot/Progetti/knp-components/vendor/doctrine-dbal/lib/Doctrine/DBAL/Connection.php:631
/home/bigfoot/Progetti/knp-components/vendor/doctrine-orm/lib/Doctrine/ORM/Query/Exec/SingleSelectExecutor.php:46
/home/bigfoot/Progetti/knp-components/vendor/doctrine-orm/lib/Doctrine/ORM/Query.php:264
/home/bigfoot/Progetti/knp-components/vendor/doctrine-orm/lib/Doctrine/ORM/AbstractQuery.php:737
/home/bigfoot/Progetti/knp-components/src/Knp/Component/Pager/Event/Subscriber/Paginate/Doctrine/ORM/QuerySubscriber.php:122
/home/bigfoot/Progetti/knp-components/vendor/Symfony/Component/EventDispatcher/EventDispatcher.php:148
/home/bigfoot/Progetti/knp-components/vendor/Symfony/Component/EventDispatcher/EventDispatcher.php:49
/home/bigfoot/Progetti/knp-components/src/Knp/Component/Pager/Paginator.php:108
/home/bigfoot/Progetti/knp-components/tests/Test/Pager/Subscriber/Sortable/Doctrine/ORM/QueryTest.php:158

3) Test\Pager\Subscriber\Sortable\Doctrine\ORM\WhitelistTest::shouldSortWithoutSpecificWhitelist
Doctrine\ORM\Query\QueryException: Invalid parameter number: number of bound variables does not match number of tokens

/home/bigfoot/Progetti/knp-components/vendor/doctrine-orm/lib/Doctrine/ORM/Query/QueryException.php:69
/home/bigfoot/Progetti/knp-components/vendor/doctrine-orm/lib/Doctrine/ORM/Query.php:255
/home/bigfoot/Progetti/knp-components/vendor/doctrine-orm/lib/Doctrine/ORM/AbstractQuery.php:737
/home/bigfoot/Progetti/knp-components/vendor/doctrine-orm/lib/Doctrine/ORM/AbstractQuery.php:538
/home/bigfoot/Progetti/knp-components/src/Knp/Component/Pager/Event/Subscriber/Paginate/Doctrine/ORM/QuerySubscriber.php:68
/home/bigfoot/Progetti/knp-components/vendor/Symfony/Component/EventDispatcher/EventDispatcher.php:148
/home/bigfoot/Progetti/knp-components/vendor/Symfony/Component/EventDispatcher/EventDispatcher.php:49
/home/bigfoot/Progetti/knp-components/src/Knp/Component/Pager/Paginator.php:108
/home/bigfoot/Progetti/knp-components/tests/Test/Pager/Subscriber/Sortable/Doctrine/ORM/WhitelistTest.php:57

--


There was 1 failure:

1) Test\Pager\Subscriber\Paginate\Doctrine\ORM\CompositeKeyTest::shouldNotBeAbleToCountCompositeKeyOnByCountQueryWalker
Failed asserting that exception of type "Doctrine\ORM\Mapping\MappingException" is thrown.


FAILURES!
Tests: 44, Assertions: 135, Failures: 1, Errors: 3, Skipped: 9.

Hello.

I have created my custom QuerySubscriber for Nativequeries.
after updating symfony to version 2.2.*
I got this Error when i launch app_dev.php enviroment

InactiveScopeException: You cannot create a service ("nws.nativequery.subscriber") of an inactive scope ("request").

Can you help me?

I'm using many Knp components in my project (at least Pager and Menu) and it's not very practical to have many one folder for each. Is it in your plans to include other components in this repository and make the s of "components" grammatically correct? :-)

Thanks!

Here d4ecf57 are some fixes with counting items. I wrote about problems with counting here: KnpLabs/KnpPaginatorBundle#187. I've done some research and found that there is a perfect soultion: http://docs.doctrine-project.org/en/latest/tutorials/pagination.html.

I think it fits our needs (like a raper ;)).

I see you use some code from them (eg,: https://github.com/KnpLabs/knp-components/blob/master/src/Knp/Component/Pager/Event/Subscriber/Paginate/Doctrine/ORM/Query/Helper.php) but maybe it will be ok to just extend Doctrine Pagination and use if version is equal or above 2.2.0.

What do you think?

Currently the Pager component offers only Doctrine's CountWalker and LimitSubqueryWalker, but there are not suitable for certain query types.

Doctrine has been using SqlWalkers (now OutputWalkers) by default in its paginator since introducing them in doctrine/orm@edd5d14

I have a query that uses HAVING that I pass onto the paginator, Doctrine is throwing a RuntimeExeption saying: "Cannot count query that uses a HAVING clause. Use the output walkers for pagination".
Changing the QuerySubscriber to use CountOutputWalker instead has fixed this for me.

Hi, sorry for my english.
Sorting do not work in latest versions.
Last working version is 1.2.5
It shows no error.
I do not know how sorting really works, but script never reach method \Knp\Component\Pager\Event\Subscriber\Sortable\Doctrine\ORM\Query\OrderByWalker::walkSelectStatement

Changes made on revision f2043e3 at QuerySubscriber causes a bug if there are repeated parameters. A query example:

...->createQuery("select s from Story s where s.published < :now and s.expired > :now")->setParameter("now", new \DateTime)

If you try to paginate this query, it will throw an invalid parameter number exception

QuerySubscriber for MongoDB ODM only accepts FIND type requests.

I had an issue with a geoloc request, so I tried to change :

if ($type !== Query::TYPE_FIND) {
throw new \UnexpectedValueException('ODM query must be a FIND type query');
}

to :

if ($type !== Query::TYPE_FIND && $type !== Query::TYPE_GEO_LOCATION) {
throw new \UnexpectedValueException('ODM query must be a FIND or GEO_LOCATION type query');
}

to accept GEO_LOCATION type requests and my pagination works perfectly.

Do you think this can cause larger issues? If not, it may warrant a commit...

(I already posted this bug in KnpPaginatorBundle, but it probably belongs to Knp-Components)

After upgrading to Symfony 2.4 beta2 i have a bug when executing:

$paginator = $this->get('knp_paginator');

See php error message below. It only occurs in DEV environment (PROD is working).
Might it maybe be related to
#68
or #88

My composer.json:
"symfony/symfony": "~2.4@dev",
"knplabs/knp-paginator-bundle": "dev-master",
"braincrafted/bootstrap-bundle": "~2.0@alpha",
[ ...]

----------- Error msg: ------------------

Catchable fatal error: Argument 1 passed to Knp\Component\Pager\Paginator::__construct() must be an instance of Symfony\Component\EventDispatcher\EventDispatcher, instance of Symfony\Component\HttpKernel\Debug\TraceableEventDispatcher given, called in C:\RunnableSSD\XAMPP\htdocs\PMU\app\cache\dev\appDevDebugProjectContainer.php on line 2148 and defined in C:\RunnableSSD\XAMPP\htdocs\PMU\vendor\knplabs\knp-components\src\Knp\Component\Pager\Paginator.php on line 45

the ORM 2.2 has a built-in pagination tool. The Knp Pager should use it when available: https://github.com/doctrine/doctrine2/blob/master/lib/Doctrine/ORM/Tools/Pagination/Paginator.php

For reference, the pagerfanta adpater has been updated: https://github.com/whiteoctober/Pagerfanta/blob/master/src/Pagerfanta/Adapter/DoctrineORMAdapter.php

Forgive me if the issues sections is not the right place for this, I thought of where else to post this but couldn't think of a better place.

Is there a way to specify a page limit of some kind? For instance: don't return results after page 500. This can be good for restricting spiders' ability to index your site which can be beneficial.

Is there a way to do this?

Much thanks.

currently pagination limit default value is harcoded in function declaration https://github.com/KnpLabs/knp-components/blob/master/src/Knp/Component/Pager/Paginator.php#L83
however passing $limit = null cause exception:
https://github.com/KnpLabs/knp-components/blob/master/src/Knp/Component/Pager/Paginator.php#L87
this is a problem when im trying to set options like this:
$paginator->paginate($query, 1, null, array(...));
basically this construction force me to specify proper $limit every time i call paginate()

it would be much better if $limit was in $defaultOptions as this way i could set $limit on one place (in Symfony2 it would be config.yml) globally for every paginator.

It should rely on the Paginator class available in Doctrine 2.2+ (2.0 and 2.1 are EOLed since several years now) instead of copying most of the logic.

Exposing the few config flag available in the class (there is 2 boolean flags) would allow users to choose the best strategy according to their needs. There is no best strategy fitting all use cases, as seen in #77 (comment)

Sorry it's a duplicate of another issue.

Fatal error: Call to a member function toArray() on a non-object in .../vendor/knplabs/knp-components/src/Knp/Component/Pager/Event/Subscriber/Paginate/Doctrine/ORM/QuerySubscriber.php on line 67

As getParameters() returns an array not an object, ->toArray() makes no sense here.

Hi . I don't know if it is pager component issue or Doctrine/ORM.
But I am using ms sql server and in this or similar count query
I get error

The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified

SELECT 
  COUNT(*) AS dctrn_count 
  FROM 
  (
    SELECT 
      DISTINCT id10 
    FROM 
      (
        SELECT 
          p0_.description AS description0, 
          p0_.short_description AS short_description1, 
          p0_.enabled AS enabled2, 
          p0_.visible AS visible3, 
          p0_.created_at AS created_at4, 
          p0_.tags AS tags5, 
          p0_.code AS code6, 
          p0_.weight AS weight7, 
          p0_.capacity AS capacity8, 
          p0_.warehouse AS warehouse9, 
          p0_.id AS id10, 
          p0_.title AS title11, 
          p0_.slug AS slug12 
        FROM 
          Product p0_ 
        LEFT JOIN 
          products_categories p1_ ON p0_.id = p1_.product_id 
        LEFT JOIN 
          Category c2_ ON p1_.category_id = c2_.id 
        ORDER BY 
          p0_.title ASC 
    ) dctrn_result 
  ) dctrn_table

thx for your reply

Encountered the following error with an order by statement on a query builder passed to the paginator. The original querybuilder had the orderby added to it, removing it made the error go away, but using the built in sorting functionality in the pagination also throws the error. Rolled back to commit of PR 50, and everything works properly.

Doctrine\DBAL\DBALException: An exception occurred while executing 'SELECT COUNT(*) AS dctrn_count FROM (SELECT DISTINCT stuffId0 FROM (SELECT s0_.stuffId AS stuffId0, s0_.code AS code1 FROM Stuff s0_ WHERE s0_.isActive = ? ORDER BY s0_.code ASC) dctrn_result) dctrn_table' with params {"1":true}:

SQLSTATE[42000]: Syntax error or access violation: 1033 [FreeTDS][SQL Server]The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified. (SQLExecute[1033] at /builddir/build/BUILD/php-5.3.10/ext/pdo_odbc/odbc_stmt.c:254) (uncaught exception) at /vendor/doctrine/dbal/lib/Doctrine/DBAL/DBALException.php line 47 [] []

When query cache is used HINT for $limitSubQuery in QuerySubscriber is not registered.

Fix in QuerySubscriber:

// line 46
$limitSubQuery = QueryHelper::cloneQuery($query);
$limitSubQuery->useQueryCache(false);

This is probably not the only place where this error occurs. And it's probably Doctrine issue related to cloning queries.

Hi Knp

I wondering is this normal?

I am only doing simple Query::setHint('knp_paginator.count', $count)

    $qb = $this->getEntityManager()->createQueryBuilder()
        ->from('TheBundle:Post', 'post')
        ->andWhere('post.type = \'post\'')
        ->andWhere('post.status = \'publish\'');

    $count = $qb->select('count(post.id)')
        ->getQuery()
        ->getSingleScalarResult();

    $itemsQuery = $qb->select('post, tax, meta')
        ->leftJoin('post.taxonomies', 'tax')
        ->leftJoin('post.metas', 'meta')
        ->orderBy('post.date', 'DESC')
        ->getQuery()
        ->setHint('knp_paginator.count', $count);

    $paginator = $this->get('knp_paginator');
    $pagination = $paginator->paginate($itemsQuery, $page, 12);

Thanks

Ka Yue

Symfony2.0.15, Doctrine2.1

Not sure if im doing something wrong here, the query works as expected from the command line and being run without the paginator used.

<?php
// Controller code
        $queryBuilder = $this->getManager()->queryAll();
        $queryBuilder->addSelect('MOD(p.random, :random) as randomOrder');
        $queryBuilder->orderBy('randomOrder');
        $queryBuilder->setParameter('random', mt_rand(100, 1000) / 100);

        $paginator = $this->get('knp_paginator');
        $profiles = $paginator->paginate(
            $queryBuilder->getQuery(),
            $request->query->get('page', 1),
            10
        );

Resulting DQL:

SELECT p, MOD(p.random, :random) as randomOrder FROM Incapable\AppBundle\Entity\Profile p ORDER BY randomOrder ASC

I ran into a problem using the get param ?sort=date on almost any page of our site that throw the exception:
Exception There is no component field [date] in the given Query because of global variable in Knp\Component\Pager\Event\Subscriber\Sortable\Doctrine\ORM\Query:56

I traced the problem to:
https://github.com/KnpLabs/knp-components/blob/master/src/Knp/Component/Pager/Event/Subscriber/Sortable/Doctrine/ORM/QuerySubscriber.php#L16

Which uses the global $_GET to see if it should sort the result. The problem is it tries to sort queries that I never intended to sort. I'm actually using the sort param to sort a query that doesn't use the paginator but since it's a global it tries to sort other queries on the page that do use the paginator and throw this error.

I fixed it by adding this to my config:

services:
    knp_paginator.subscriber.sortable:
        class: SomeDummyClass\SortableSubscriber
        scope: request
        tags:
             - { name: 'knp_paginator.subscriber' }      

where SomeDummyClass\SortableSubscriber just has empty before function and doesn't register any listeners.

I think all the Sortable subscribers should probably be rewritten to not use global variables and instead pull the sort fields and directions from the options.

If there's a better way to work around this let me know but since I don't plan on using
the sorting functions of the paginator turning of the subscribers seems like the best option.

Example of use:

I have a CreateUser form and a paginated list of "find All Roles Except Roles Owned by User".

Lets say I have total 11 roles. Paginator limit is set to 10. So I have 2 pages. (page 1 with 1-10 and page 2 with 11).

Now, I add 2 roles to User with JAVASCRIPT (remove table row from Roles List, add new form item to user).

Next, I click on "go to page 2" link. It triggers an AJAX call (that should return new list).

The AJAX call also submits the form (so, my controller is aware of the roles i added).

So, now my "find All Roles Except Roles Owned by User" is 9 items long (becouse I added 2 roles to user). Page 2 does not exist (its empty).

What I would expect in this situation is to see the remaining 9 roles (which means, show page 1).

Ofcourse, I could write this into my code, but i believe it should be inside the core paginator code (in the paginate function).

When using DBAL for getting data for Knp pagination via DBALQueryBuilderSubscriber it fails when sort is used in a sql query when querying from MSSQL view. View requires some sort of data limit set when doing count. Obviously in count query we need all records so can't use limit like 'FETCH', 'TOP',... When sort is removed Knp pagination works OK. Here is example:

`

  // using custom Symfony connection service provider for MSSQL
   $dbMSSQL = $this->get('dbMSSQL.service');

   //get DBAL query builder
    $qb = $dbMSSQL->getQueryBuilder();

    $qb ->select('t.id',  't.customer', 't.supplier', 't.name')
        ->from('InternalDB.dbo.ViewSupplierServices', 't')
        ->orderBy('t.customer')
        ;`

Query fails, since sort by on view count requires limit like TOP, FETCH.

Hello. I created a query using Doctrine2 (2.3 and above version) Arbitrary join syntax

$productQueryBuilder->leftJoin("NeonusNwsProductPriceBundle:Price", "pp", \Doctrine\ORM\Query\Expr\Join::WITH, 'p.id=pp.product');

and I got an error with this text
"Cannot count query which selects two FROM components, cannot make distinction"

Can you please help me how to make a workaround for this?
I need this kind of join to sort my Product table according Price table that has an unidirectional ManyToOne reference to product, (Because of my modular architecture) therefor I cannot use normal leftjoin.

Thank you for your reply and suggestions

Hi,
there is an issue with resolving Doctrine entities in DQL queries to their primary key - which works in 1.2.1. It's reproducable with the KnpPaginatorBundle when the target query builder contains something as follows:

$qb->addWhere("u.group = :group")->setParameter('group', $groupEntity);

The query part is not resolved to u.group_id = 3 (supposed we have the group ID 3) as before because the raw SQL query and parameters are used now in Pager/Event/Subscriber/Paginate/Doctrine/ORM/QuerySubscriber.php in line 76:

$countResult = $conn
    ->executeQuery($countQuery->getSQL(), 
            $params,
            $types)
    ->fetchColumn();

So filtering by an entity fails and I get an empty result.

Workaround is to stick to v.1.2.1 or to assign the complete query result as target instead of the query builder. But the latter is obviously very slow in big results sets.

It would be nice having something like Knp\Component\Pager\PaginatorInterface, and Knp\Component\Pager\Paginator implementing it, to make mocking easier when unit testing.