CODEWITHSUNDEEP
phppostgresqlsymfonydoctrine-ormdoctrine
Muhammad Taqi
Muhammad Taqi

Reputation: 5424

Symfony Doctrine Group By Query

I have below sql query running fine, 

SELECT completed_by, count(*) AS Total
FROM tasks
WHERE completed_by is not null AND status = 1
GROUP BY completed_by
;

Em am doing it with doctrine query builder, but not working returning an error.

$parameters = array(
                    'status' => 1,
                );

$qb = $repository->createQueryBuilder('log');
$query = $qb
->select(' log.completedBy, COUNT(log) AS Total')
->where('log.Status = :status')
->groupBy('log.completedBy')
->setParameters($parameters)
->getQuery();

and getting below error;

[Semantical Error] line 0, col 21 near 'completedBy,': Error: Invalid PathExpression. Must be a StateFieldPathExpression.

Upvotes: 6

Views: 29303

Answers (3)

Tafsir Ndiour
Tafsir Ndiour

Reputation: 21

When you want to select a column who is a fk for another table (entity), use the IDENTITY function instead of the column name only. Example: In your case

$parameters = array(
                    'status' => 1,
                );

$qb = $repository->createQueryBuilder('log');
$query = $qb
    ->select('IDENTITY(log.completedBy), COUNT(log.something) AS Total')
    ->where('log.Status = :status')
    ->groupBy('log.completedBy')
    ->setParameters($parameters)
    ->getQuery();

Upvotes: 2

Doried Abd-Allah
Doried Abd-Allah

Reputation: 91

I know this answer can be late, but I struggled with the exact same problem, and did not find any answer on the internet, and I believe a lot of people will struggle in this same issue.

I'm assuming your "completedBy" refers to another entity.

So, inside your repository, you can write:

$query = $this->createQueryBuilder("log")
              ->select("completer.id, count(completer)")
              ->join("log.completedBy", "completer")
              ->where('log.Status = :status')
              ->groupBy("completer")
              ->setParameters($parameters)
              ->getQuery();

This will compile to something like:

SELECT completer.id, count(completer) FROM "YOUR LOG CLASS" log INNER JOIN log.completedBy completer WHERE log.Status=:status GROUP BY completer

Now, You can do another query to get those 'completers', by their ids.

Upvotes: 6

michaJlS
michaJlS

Reputation: 2500

This is wrong: COUNT(log) AS Total. It should be something like COUNT(log.log) AS Total.

Upvotes: 0

Related Questions