Symfony Doctrine Group By Query

Question

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.

Solution

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.

Comments

[michaJlS]

Do you have column "log" in your "log" table? If not, then you should probably count(log.id) or something like that.

[Muhammad Taqi]

yes it is id column, i replaced it , but still the same error at ->groupBy('log.completedBy')

[michaJlS]

But it is not error for groupBy(), it is error for select(). Look at the message: completedBy, - you have a comma here, it appears in select clause, not in grouping clause.

[Admin]

Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center.