Orm: Paginator count returns zero when using instance of

[pedrobrost]

I have a query with something like this:

$qb = $this->createQueryBuilder('load')
            ->select('load')
            ->andWhere('load INSTANCE OF :type')
            ->setParameter('type', $this->getEntityManager()->getClassMetadata('\GRAL\LoadBundle\Entity' . $type))
            ->setFirstResult(($page - 1) * self::MAX_PER_PAGE)
            ->setMaxResults(self::MAX_PER_PAGE)
            ->orderBy('load.createdAt');

return new Paginator($qb->getQuery(), true);

It works perfectly when I iterate over the paginator, the results are correct, but the count result is always zero:

count($paginator); // returns 0

paginator|length <!-- returns 0 -->

This only happens when I use INSTANCE OF. I'm using single table inheritance.

[Ocramius]

Can you check the generated SQL?

[pedrobrost]

SELECT m0_.id AS id_0, m0_.loadDate AS loadDate_1, m0_.createdAt AS createdAt_2, m0_.store_id AS store_id_3, m0_.is_draft AS is_draft_4, m0_.gang_id AS gang_id_5, m0_.dest_store_id AS dest_store_id_6, m0_.discr AS discr_7, m0_.owner_id AS owner_id_8 FROM materials_load m0_ WHERE (m0_.discr IN (?)) AND m0_.discr IN ('gangLoad', 'storeLoad') ORDER BY m0_.createdAt ASC LIMIT 15 OFFSET 0

[Ocramius]

And what if you iterate over results? What is the query? Are there results at all?

[Ocramius]

Overall, this looks like an issue in inheritance and criteria applied to it, but we'd need to have a failing test case to attack it. Can you please see if writing one similar to the ones in https://github.com/doctrine/doctrine2/tree/master/tests/Doctrine/Tests/ORM/Functional/Ticket is feasible on your end?

[pedrobrost]

If I iterate over results I get all as expected, the count is the only problem. I will see how to write one of that tests, first time ;)

I'd like to mention this:

In Doctrine\ORM\Tools\Pagination class, in getCountQuery() if I replace this:

if ($this->useOutputWalker($countQuery)) {
    $platform = $countQuery->getEntityManager()->getConnection()->getDatabasePlatform(); // law of demeter win

    $rsm = new ResultSetMapping();
    $rsm->addScalarResult($platform->getSQLResultCasing('dctrn_count'), 'count');

    $countQuery->setHint(Query::HINT_CUSTOM_OUTPUT_WALKER, 'Doctrine\ORM\Tools\Pagination\CountOutputWalker');
    $countQuery->setResultSetMapping($rsm);
} else {
    $this->appendTreeWalker($countQuery, 'Doctrine\ORM\Tools\Pagination\CountWalker');
}

for only this:

$this->appendTreeWalker($countQuery, 'Doctrine\ORM\Tools\Pagination\CountWalker');

It works perfectly (I don't understand what is happening).

Thanks, and sorry for my english, I will see how to create the test

[Ocramius]

@pedrobrost that at least isolates the bug to Doctrine\ORM\Tools\Pagination\CountOutputWalker

Also, make sure you try your test against 2.6 and master :-)

[pedrobrost]

I have not done the test yet, but I think the problem is setting the parameter, as @eheuje says here, if we set the parameter using getClassMetadata(), the count query won't work.

[sdespont]

This problem still exists in version 2.6.1

[jerkan]

I found a workaround using queryBuilder expr method:
->where($queryBuilder->expr()->isInstanceOf('alias', Classname::class))