SELECT DISTINCT returns 0 id and can not be disabled

[webdevilopers]

We are using a rather complicated query with a lot of grouping. I will break it down to most interesting parts:

    public function getInspectionResultQuery(Specification $specification, ResultModifier $modifier = null)
    {
        $qb = $this->_em->createQueryBuilder();
        $qb->select([
                $this->alias . '.id',
                $this->alias . '.date',
                sprintf("GroupConcat(DISTINCT %s.comment) AS comment", $this->alias)
        ])
            ->from($this->_entityName, $this->alias)
            ->join(sprintf("%s.parttype", $this->alias), 'part')
            ->join(sprintf("%s.location", $this->alias), 'testLocation')
            ->addGroupBy($this->alias . '.date')
            ->addGroupBy($this->alias . '.shippingnumber')
            ->addGroupBy('part.id')
            ->addGroupBy('testLocation.id')
        ;

        $this->applySpecification($qb, $specification);
        $query = $qb->getQuery();

        if ($modifier !== null) {
            $modifier->modify($query);
        }

        return $qb->getQuery();
}

We are using this DQL at two places in our application. Both use the same filters (specifications). The first one works as expected and returns the important parts:

SELECT b0_.id AS id_0, b0_.date AS date_1, ...
WHERE b0_.compartment_id = ? AND (c6_.id = ? AND (b0_.date >= ? AND b0_.date <= ?) AND l3_.id = ? AND p2_.id = ?)
GROUP BY b0_.date, b0_.shippingnumber, p2_.id, l3_.id
ORDER BY b0_.date DESC, p2_.integrator_number ASC

We are using the default paginator option with use-distinct set to true. The COUNT of the paginator works fine and returns the correct result using DISTINCT:

SELECT count(DISTINCT b0_.id)

Unfortunately we don't get any results from this correctly counted rows. The responsible query will look like this:

SELECT DISTINCT b0_.id AS id_0...

And this will result in the following original query with the extra IN condition for selecting the found IDs:

SELECT b0_.id AS id_0, b0_.date AS date_1, ...
WHERE ...
AND b0_.id IN (?)
GROUP BY b0_.date, b0_.shippingnumber, p2_.id, l3_.id ORDER BY b0_.date DESC, p2_.integrator_number ASC

Parameters: [1, 6408, '2015-07-30', '2016-07-06', 4, 13295, ['0']]

The issue is caused by the DISTINCT on the SELECT. Without it the query will work fine. We tried to disable it but this will only remove DISTINCT on the COUNT query.

We need the DISTINCT removed on the SELECT IDs query.

Is (/ should) this (be) possible?

P.S.: We are using a mixed result but the (Doctrine) Pagniator seems to have no problem with it until we enable wrap-queries. That would return Not all identifier properties can be found in the ResultSetMapping: id. Maybe this is important to know too.

Possibly related:

• https://github.com/KnpLabs/KnpPaginatorBundle/issues/298
• https://github.com/KnpLabs/knp-components/pull/126
• https://github.com/KnpLabs/KnpPaginatorBundle/issues/123

[polc]

Hello sorry for the late response! Have you solved your issue? Under the hood we use Doctrine paginator, so it may be an issue with the Doctrine\ORM\Tools\Pagination\Paginator.

It look to me that the DISTINCT part of your query is important because you have JOINs and you may get the wrong number of result.

We fixed some issues about GROUP BY since your issue you may give a try to the last version of the bundle.

[webdevilopers]

We have refactored our codebase and another developer was involved. I will ask him in October if we still use the query and try the latest version. Thanks!