I’m using Symfony 6 and API Platform and encountered some difficulties sorting my entity with calculated joined table’s column.

Here’s the tables

Company

+----+------------+--------------+--------------+
| id | name       | industry     | location     |
+----+------------+--------------+--------------+
| 1  | TechCorp   | Technology   | New York     |
| 2  | HealthPlus | Healthcare   | San Francisco|
+----+------------+--------------+--------------+

Opening

+----+------------+-------------------+-----------+
| id | company_id | title             | positions |
+----+------------+-------------------+-----------+
| 1  | 1          | Software Engineer | 1         |
| 2  | 1          | Data Scientist    | 5         |
| 3  | 2          | Nurse             | 2         |
| 4  | 2          | Teacher           | 1         |
| 5  | 1          | Administrator     | 5         |
| 6  | 1          | Administrator     | 5         |
+----+------------+-------------------+-----------+

I want to display the companies by their number of positions, so I made this query

SELECT c.*, SUM(o.positions) AS 'job_positions'
FROM hira_db.company c
LEFT JOIN hira_db.opening o ON o.company_id = c.id
GROUP BY c.id
ORDER BY job_positions DESC

so it will generate something like this

+----+------------+--------------+--------------+---------------+
| id | name       | industry     | location     | job_positions |
+----+------------+--------------+--------------+---------------+
| 1  | TechCorp   | Technology   | New York     | 16            |
| 2  | HealthPlus | Healthcare   | San Francisco| 3             |
+----+------------+--------------+--------------+---------------+

But when applying the same query to doctrine, it messes up the pagination count

I made a DoctrineExtension and add these query

$alias = $queryBuilder->getRootAliases()[0];
$queryBuilder
    ->innerJoin(sprintf('%s.openings', $alias), 'job')
    ->addSelect('SUM(job.positions) AS HIDDEN job_positions')
    ->orderBy('job_positions', 'DESC')
    ->addGroupBy(sprintf('%s.id', $alias));
// default join column generated automatically by doctrine?
$queryBuilder->addGroupBy('openings_a1.id');

this returns

{
    "@context": "/api/contexts/Company",
    "@id": "/api/companies",
    "@type": "hydra:Collection",
    "hydra:totalItems": 6,

instead of

{
    "@context": "/api/contexts/Company",
    "@id": "/api/companies",
    "@type": "hydra:Collection",
    "hydra:totalItems": 2,

I only have 2 companies and 6 openings