0

I am trying to change the returned array from a doctrine query from:

['city' => 'Warsaw', '1' => '2']

to:

['city' => 'Warsaw', 'count' => '2']

My query looks like this:

$queryBuilder = $this->createQueryBuilder('geo');
$queryBuilder->select([
    'geo.city',
    $queryBuilder->expr()->countDistinct('geo.id')
]);
$queryBuilder->groupBy('geo.city');
$result = $queryBuilder->getQuery()->getResult();

Not sure how to correctly write a AS count into this subexpression.

tobias47n9e
  • 2,233
  • 3
  • 28
  • 54

1 Answers1

1

The Expr class is nothing more than a set of helpers to write your expressions. Nothing prevents you from combining it with strings:

// $qb your query builder
$qb->addSelect($qb->expr()->countDistinct('geo.id') . ' AS geo_count');

If you don't like mixing expr and strings, you could even write the DQL directly:

$qb->addSelect('COUNT(DISTINCT geo.id) AS geo_count');

Note: I used geo_count as the alias since you can't use count as it will be interpreted as the COUNT function by the DQL parser.

Alan T.
  • 1,382
  • 1
  • 7
  • 14
  • I was looking for something like `countDistinct('geo.id')->as(geo_count)`, but your solution works well, although it would be nice to avoid the stringiness of both solution. Thanks! – tobias47n9e Feb 19 '18 at 12:21
  • 1
    @tobias47n9e Yeah I was searching for the same [in the doc](http://docs.doctrine-project.org/projects/doctrine-orm/en/latest/reference/query-builder.html#the-expr-class) but the `expr` class's `countDistinct` method only expect one argument and there is no `as` method as you can see. I guess someone could try to implement it and send a PR for that :) – Alan T. Feb 19 '18 at 12:43