We have moved our forum to GitHub Discussions. For questions about Phalcon v3/v4/v5 you can visit here and for Phalcon v6 here.

Subquery with builder

Hi guys!

I am looking for an example for subqueries used with Builder (Phalcon\Mvc\Model\Query\Builder). Have to use a subquery with paginator (Phalcon\Paginator\Adapter\QueryBuilder). Somebody here who can help!?

Thanks



145.0k
Accepted
answer

There is some example from my app:

  $cars = $this->modelsManager->createBuilder()
            ->columns(array('Car.id', 'model', 'CarBrand.name', 'CarBody.name as body', 'engine', 'power', 'Car.addDate', 'soldDate',
                'buyPrice', 'soldPrice', 'price',
                "(CASE soldPrice IS NULL WHEN TRUE THEN 0 ELSE (price-soldPrice) END) as priceDiff",
                'IFNULL((SELECT SUM(value) FROM Samochody\Models\CarFinance\CarFinance WHERE car = Car.id GROUP BY
                        Car.id),0) as financeCost',
                'IFNULL((SELECT SUM(value) FROM Samochody\Models\CarFinance\CarFinance WHERE car = Car.id
                        GROUP BY Car.id),0)
                        +IFNULL(buyPrice,0) as totalCost',
                'IFNULL(soldPrice,price)-
                        (IFNULL((SELECT SUM(value) FROM Samochody\Models\CarFinance\CarFinance WHERE car = Car.id
                        GROUP BY Car.id),0)
                        +IFNULL(buyPrice,0)) as profit'))
            ->from(array('Car' => 'Samochody\Models\Car\Car'))
            ->leftJoin('Samochody\Models\CarBrand\CarBrand', null, 'CarBrand')
            ->leftJoin('Samochody\Models\CarBody\CarBody', null, 'CarBody')
            ->where($handledParams['where'], $handledParams['bind'])
            ->orderBy('Car.addDate')
            ->getQuery()
            ->execute()
            ->toArray();
        return $cars;

Thank you very much!