[SOLVED] Cross Join to DQL

Cross Join to DQL

I'm trying to convert this I think simple mysql query into Doctrine dql, however, Im experience quite a struggle right now...

SELECT (c.prix-aggregates.AVG) AS test 
FROM immobilier_ad_blank c 
CROSS JOIN (
     SELECT AVG(prix) AS AVG 
     FROM immobilier_ad_blank) 
AS aggregates

Purpose of this: creating z-score. Original implementation coming from this question Calculating Z-Score for each row in MySQL? (simple)

I thought about creating an association within the entity, but I mean its not necessary, its only for stats.

Edit: Btw, I dont wanna use raw SQL, I will extract the "subquery" from another query builder expression using getDQL. Otherwise, I will have to rewrite my dynamic query builder to take in account for rawSQL.

Edit 2: Tried this

$subQb = $this->_em->createQueryBuilder();
$subQb->addSelect("AVG(subC.prix) as AMEAN")
      ->from("MomoaIntegrationBundle:sources\Common", "subC");
$subDql = $subQb->getDQL();

$dql = "SELECT c.prix FROM MomoaIntegrationBundle:sources\Common c INNER JOIN ($subDql) AS aggregates";

Raw dql is:

SELECT c.prix FROM MomoaIntegrationBundle:sources\Common c INNER JOIN (SELECT AVG(subC.prix) as AMEAN FROM MomoaIntegrationBundle:sources\Common subC) AS aggregates

Getting this strange error:line 0, col 70 near '(SELECT AVG(subC.prix)': Error: Class '(' is not defined.

Edit 3: I found kinda of a hawkish way to make it work but doctrine is stubborn with its implementation of entities and such and forgot that STATISTICS do NOT need ENTITIES !

     $subQb = $this->_em->createQueryBuilder();
    $subQb->addSelect("AVG(subC.prix) as AMEAN")
            ->from("MomoaIntegrationBundle:sources\Common", "subC");

    $sql = "SELECT (c.prix-aggregates.sclr_0) AS test FROM immobilier_ad_blank c CROSS JOIN "
            . "({$subQb->getQuery()->getSQL()}) AS aggregates";
    $stm = $stm = $this->_em->getConnection()->prepare($sql);
    $stm->execute();
    $data = $stm->fetchAll();

If you have a better solution, Im all ears ! I actually dislike this solution.

Solution

For complex queries you might want to consider bypassing DQL and using a native query - especially since you don't need the result in an entity.

$connection = $em->getConnection();

$statement = $connection->prepare("
    select c.prix-aggregates, t1.avg 
    from immobilier_ad_blank
    cross join (
        select avg(prix) as avg
        from immobilier_ad_blank
    ) t1
");

$statement->execute();

$results = $statement->fetchAll();