Symfony Doctrine DQL how to add SELECT in a SUM of a main SELECT

Actually, i would like to reproduce with Doctrine this regular SQL Query :

SELECT SUM(b.nb_places * (SELECT pricing FROM param p WHERE t.date BETWEEN p.from_date AND p.to_date)) as gains from booking b INNER JOIN tour t ON b.tour_id = t.id;

That compute total gains according prices between two dates.

I wrote this DQL in a Repository :

    public function allBooking() {
        $query = $this->manager->createQuery(
            'SELECT 
                 SUM(b.nbPlaces * SELECT p.pricing FROM AppEntityParam p WHERE t.date BETWEEN p.fromDate AND p.toDate)
        FROM AppEntityBooking b JOIN b.tour t'
    );
    return $query->getResult();
}

But running this query, i got :

[Syntax Error] line 0, col 24: Error: Expected Literal, got 'SELECT' (500 Internal Server Error)

How do i acheive this query with DQL or using QueryBuilder ?

Thx for help

Source: Symfony Questions

Was this helpful?

0 / 0

Leave a Reply 0

Your email address will not be published. Required fields are marked *