Doctrine QueryBuilder - multiple select fields

Question

I have a strange problem with multiple select fields. This is my query:

$query = $queryBuilder
            ->select(
                [
                    'SUM(t1.quantity) AS quantity_t1',
                    't2.currency AS currency_t2',
                    'SUM(t1.amount - t1.promotionDiscount) AS amount_t1',
                    'SUM(ROUND((t1.amount - t1.promotionDiscount) / ((100 + t2.tax) / 100), 2)) AS amount_net',
                    'SUM(ROUND((t1.amount - t1.promotionDiscount) / '.EXCHANGE_RATE_GBP.', 2)) AS amount_gbp',
                    'SUM(ROUND(ROUND((t1.amount - t1.promotionDiscount) / '.EXCHANGE_RATE_GBP.', 2) / ((100 + t2.tax) / 100), 2)) AS amount_gbp_net'
                ]
            )
            ->from(Sale::class, 't2')
            ->join(
                SaleRow::class,
                't1',
                Join::WITH,
                $queryBuilder->expr()->andX(
                    $queryBuilder->expr()->eq('t1.saleId', 't2.id')
                )
            )
            ->where(
                $queryBuilder->expr()->between('t2.purchaseTime', ':start', ':end')
            )
            ->andWhere(
                $queryBuilder->expr()->in('t2.orderStatus', ':status')
            )
            ->setParameters(
                [
                    ':start' => $startDate->format('Y-m-d H:i:s'),
                    ':end' => $endDate->format('Y-m-d H:i:s'),
                    ':status' => ['pending', 'shipped']
                ]
            )
            ->getQuery();

        $results = $query->getArrayResult();

The query works, but only If I change my select array:

For example like this:

->select(
    [
       't1.id',
       't2.id'
    ]
 )

And very strange... This works:

->select(
    [
        'SUM(t1.quantity) AS quantity_t1',
        'SUM(t1.amount - t1.promotionDiscount) AS amount_t1'
    ]
)

And this too:

->select(
    [
        't2.currency AS currency_t2',
    ]
)

But .. this does not work:

->select(
    [
        'SUM(t1.quantity) AS quantity_t1',
        't2.currency AS currency_t2',
    ]
)

Why?

score 1 · Accepted Answer · edited May 23 '17 at 12:09

1

You cannot mix Aggregates with non-aggregates in a SQL query.

Because the aggregate will return one value. The non-aggregate will return each values.

How to mix these results in the response ?

Other problem, you use a ROUND() function in DQL. But this function does not exist, you have to register it a custom DQL function.

See this question.

edited May 23 '17 at 12:09

Community

1
1

answered Aug 08 '16 at 08:15

Alsatian

3,086
4
25
40

ok thanks.. but when I remove the non-aggregate select line (t2.currency as currency_t2) it also not working... – goldlife Aug 08 '16 at 08:38
I got an exception from doctrine ... "expected known function got round" – goldlife Aug 08 '16 at 08:46
There is no ROUND function in doctrine ... You have to create it, see here : http://stackoverflow.com/a/15630221/3554065 – Alsatian Aug 08 '16 at 08:48

Doctrine QueryBuilder - multiple select fields

1 Answers1