Go to DQL

Question

I am trying to convert this, I think, a simple mysql query to a Doctrine dql, however Im now having a pretty serious fight ...

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 

The purpose of this: creating a z-score. Original implementation coming from this question. Calculation of Z-Score for each row in MySQL? (plain)

I was thinking about creating an association within an entity, but I mean it is not necessary, it is only for statistics.

Edit: Btw, I don't want to 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 into account 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:

 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 a kind of furious way to make it work, but the teaching is stubborn with its implementation of entities, etc., And I forgot that STATISTICS ARE NECESSARY!

  $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 really don't like this solution.

Answer 1

For complex queries, you can consider bypassing DQL and using your own query - especially since you do not need a result in essence.

 $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(); 

Answer 2

Starting with Doctrine 2.4, you can use JOIN without using a specific association, for example:

 SELECT u FROM User u JOIN Items i WITH u.age = i.price 

It makes no sense, but you understand. The WITH keyword is absolutely necessary in this case, otherwise it is a syntax error, but you can just provide a dummy condition, for example:

 SELECT u FROM User u JOIN Items i WITH 0 = 0 

This substantially leads to cross-coupling. Whether this is a good idea in this situation is another question, but I came across situations where it was really very useful.