DataTableRepository fails on DISTINCT

[cirolaferrara]

If $query contains DISTINCT clause, recordsTotal and recordsFiltered displays wrong value.

[odan]

What have you tried so far?

[cirolaferrara]

Hi @odan. This is the database dump This is the method into Repository This is the result This is what I think must have

[odan]

Ok this is a special use case. DISTINCT is implemented by GROUP BY under the hood.

The SQL of your query looks like this:

SELECT `people`.* FROM `people_link`
LEFT JOIN `people` `people` ON people_link.id_people = people.id
GROUP BY `people`.`id`

So this query would give the same result:

$query = $this->queryFactory->newSelect('people_link');
$query->select(['people.*'])
    ->group('people.id')
    ->leftJoin('people', 'people_link.id_people = people.id');

Your requirement is to get count of number of groups. So we need two operations-

• group(inner query)
• count(outer query)

To fix the counter you have to open the class DataTableRepository and then replace this line:

$countQuery = clone $query;

with this line:

$countQuery = $this->queryFactory->newQuery()->from(['sub' => $query]);

Resulting SQL for the count query:

SELECT (COUNT(*)) AS `count` FROM (SELECT `people`.* 
FROM `people_link` 
LEFT JOIN `people` `people` ON people_link.id_people = people.id 
GROUP BY `people`.`id` ) `sub`

This fix should give you the correct count value:

[cirolaferrara]

Perfect! Thank you 👍