pagination not work with groupBy

[exbbxak]

Hi, after remove line

->from('(' . $sql . ')', 'dbal_count_tbl')

in DBALQueryBuilderSubscriber pagination not work if in queryBuilder use groupBy

[garak]

Please provide an example

[exbbxak]

$qb = $this->connection->createQueryBuilder()
->select(
'm.id',
'TRIM(CONCAT( m.name_last, ' ', m.name_first, ' ', m.name_middle)) AS "name"',
'm.name_last AS "lastName"',
'm.name_first AS "firstName"',
'm.name_middle AS "middleName"',
'm.email',
'm.status',
'mg.name as groupName',
'mr.name as roleName',
'u.last_activity_at as lastActivityAt',
'u.role',
)
->from('lms2_members_members', 'm')
->leftJoin('m', 'user_users', 'u', 'm.id = u.id')
->leftJoin('m', 'lms2_members_members_groups', 'mmg', 'mmg.member_id = m.id')
->leftJoin('mmg', 'lms2_members_groups', 'mg', 'mmg.group_id = mg.id')
->leftJoin('m', 'lms2_members_members_roles', 'mmr', 'mmr.member_id = m.id')
->leftJoin('mmr', 'lms2_members_role', 'mr', 'mmr.role_id = mr.id');
$qb->groupBy('m.id');
if (!\in_array($sort, ['name', 'email', 'id', 'status', 'lastActivityAt', 'groupName', 'roleName'], true)) {
throw new \UnexpectedValueException('Cannot sort by ' . $sort);
}
$qb->orderBy($sort, $direction === 'desc' ? 'desc' : 'asc');
return $this->paginator->paginate($qb, $page, $size);

[garak]

Well, I know myself what a groupBy is, merely showing a query doesn't help too much to understand the problem.

[exbbxak]

ok sorry, problem in totalCount in pagination

if in DBALQueryBuilderSubscriber on line 35 add ->from('(' . $sql . ')', 'dbal_count_tbl') from v4.3.1 all good work

but if remove it line totalCount shows incorrect data

and pagination not work

[Evgeny1973]

Hha, looks like my problem

[garak]

As commented in the related PR #333, the from has been removed on purpose.

Someone else has had the same issue with other paginators, see this doctrine/dbal#6371
It looks like the only way to fix this problem is injecting the DBAL Connection in the subscriber and re-building the count query from scratch.

Tagging @W0rma as the original proposer for the PR linked above

[garak]

Please check if the proposed fix works for you (I tried it myself and it works for me):

execute composer require --no-update knplabs/knp-components "dev-fix-dbal4 as 4.4.1"
add the following to your composer json:

"repositories": [
    {
        "type": "vcs",
        "url": "https://github.com/garak/knp-components.git"
    }
],

manually edit your vendor/knplabs/knp-paginator-bundle/config/paginator.xml file, adding an optional argument to the knp_paginator service. The service configuration should look like the following:

<service id="knp_paginator" class="Knp\Component\Pager\Paginator" public="true" lazy="true">
    <argument type="service" id="event_dispatcher" />
    <argument type="service" id="Knp\Component\Pager\ArgumentAccess\ArgumentAccessInterface" />
    <argument type="service" id="database_connection" on-invalid="null" /> <!-- this is the new line -->
    <tag name="proxy" interface="Knp\Component\Pager\PaginatorInterface" />
 </service>

run composer update

[Evgeny1973]

Yes!!!

[exbbxak]

You are awesome! it work! thanks!

[Evgeny1973]

@garak When will the fix be available?

[garak]

@garak When will the fix be available?

It's not so straightforward.
The PR is merged now, so you can use our dev-master alias (instead of the fork mentioned before).
But we can't release it in a patch or minor version, because it's a BC-break. Since we need a major version, I'd like to close other issues before.

Beside that, we need a PR to add the new argument to the bundle.

[Evgeny1973]

Ok, added a line to the composer,json for now, waiting for release info.
"knplabs/knp-paginator-bundle": "^6.0",
"knplabs/knp-components": "dev-master",

[Evgeny1973]

Oh.
On dev server

$qb
           ->groupBy('p.id, t.id')
            ->orderBy($sort, 'asc' === $direction ? 'asc' : 'desc') ;
       
return $this->paginator->paginate($qb, $page, $size);

i've got a error:
One of listeners must count and slice given target

On local comp all works fine.