CODEWITHSUNDEEP
symfonydoctrine-orm
Splendonia
Splendonia

Reputation: 1369

Server crashes when applying complex doctrine subquery

I'm trying (unsuccessfully) to do this:

SELECT AVG (DISTINCT(DATEDIFF(E1.updated_date, E2.updated_date))) FROM 

(SELECT DISTINCT e1_.updated_date, e2_.id as id FROM engineering_details e1_ INNER JOIN engineerings e2_ ON e1_.engineering_id = e2_.id INNER JOIN projects p4_ ON e2_.project_id = p4_.id INNER JOIN `lines` l5_ ON e2_.line_id = l5_.id INNER JOIN statuses s6_ ON e1_.to_status_id = s6_.id WHERE e2_.Rev_asignee = 'Test User 1 [Eduardo Monto]' AND p4_.id = 8 AND l5_.id = 1 AND e2_.out_of_plan = 0 AND e1_.updated_date >= '2014-01-01' AND e1_.updated_date <= '2014-06-11' AND s6_.id = 16) as E1,

(SELECT DISTINCT e1_.updated_date, e2_.id as id FROM engineering_details e1_ INNER JOIN engineerings e2_ ON e1_.engineering_id = e2_.id INNER JOIN projects p4_ ON e2_.project_id = p4_.id INNER JOIN `lines` l5_ ON e2_.line_id = l5_.id INNER JOIN statuses s6_ ON e1_.to_status_id = s6_.id WHERE e2_.Rev_asignee = 'Test User 1 [Eduardo Monto]' AND p4_.id = 8 AND l5_.id = 1 AND e2_.out_of_plan = 0 AND e1_.updated_date >= '2014-01-01' AND e1_.updated_date <= '2014-06-11' AND s6_.id = 38) AS E2

WHERE E1.id = E2.id

in DQL

So far, I've 'translated' to what I think it's the DQL equivalent of the SQL Above. I was using createQuery instead of createQueryBuilder but I read it was the only way of applying 'subqueries' so I ended up changing to createQueryBuilder instead. When using createQuery I got an error of 'CLASS ( not found'

So, I'm doing

 $qb = $em->createQueryBuilder();
            $query = $qb->select('SELECT AVG(DISTINCT(DATEDIFF(E1.updatedDate, E2.updatedDate ))) AS AVGTREV')
                    ->from($qb->select('SELECT DISTINCT T1.updatedDate, eng.id as id HW\Bundle\EngMgmtBundle\Entity\EngineeringDetail T1 INNER JOIN T1.engineering eng INNER JOIN eng.project p INNER JOIN eng.line l INNER JOIN T1.toStatus ts WHERE eng.ReviewAsignee = :eng AND ts.id = 16
AND p.id = :project_id AND l.id = :line_id AND eng.outOfPlan = 0 AND T1.updatedDate >= :period_from AND T1.updatedDate <= :period_to'), 'E1')
                    ->from($qb->select('SELECT DISTINCT T2.updatedDate, eng.id as id HW\Bundle\EngMgmtBundle\Entity\EngineeringDetail T2 INNER JOIN T2.engineering eng INNER JOIN eng.project p INNER JOIN eng.line l INNER JOIN T2.toStatus ts WHERE eng.ReviewAsignee = :eng AND ts.id = 38
AND p.id = :project_id AND l.id = :line_id AND eng.outOfPlan = 0 AND T2.updatedDate >= :period_from AND T1.updatedDate <= :period_to'), 'E2')
                    ->setParameters(array('project_id' => $project, 'line_id' => $line, 'eng' => $user->getName(), 'period_from' => $period_from, 'period_to' => $period_to))
                    ->getQuery();
            $entities = $query->getResult();

I'm aware I shouldn't be using 2 'from' however I can't seem to find the way to do it otherwise, since the main select uses the results from the two subqueries.

So, when I try to make the query via the interface, It doesn't throw an error but, it stays 'loading' and then changes to 'Website Unavailable'... I'm pretty sure it's the query. What am I doing wrong?

Upvotes: 1

Views: 143

Answers (3)

Splendonia
Splendonia

Reputation: 1369

$sql = "SELECT AVG (DISTINCT(DATEDIFF(E1.updated_date, E2.updated_date))) AS AVGTREV FROM

(SELECT DISTINCT e1_.updated_date, e2_.id as id FROM engineering_details e1_ INNER JOIN engineerings e2_ ON e1_.engineering_id = e2_.id INNER JOIN projects p4_ ON e2_.project_id = p4_.id INNER JOIN `lines` l5_ ON e2_.line_id = l5_.id INNER JOIN statuses s6_ ON e1_.to_status_id = s6_.id WHERE e2_.Rev_asignee = :engineer AND p4_.id = :project AND l5_.id = :line AND e2_.out_of_plan = 0 AND e1_.updated_date >= :begin_date AND e1_.updated_date <= :end_date AND s6_.id = 16) as E1,

(SELECT DISTINCT e1_.updated_date, e2_.id as id FROM engineering_details e1_ INNER JOIN engineerings e2_ ON e1_.engineering_id = e2_.id INNER JOIN projects p4_ ON e2_.project_id = p4_.id INNER JOIN `lines` l5_ ON e2_.line_id = l5_.id INNER JOIN statuses s6_ ON e1_.to_status_id = s6_.id WHERE e2_.Rev_asignee = :engineer AND p4_.id = :project AND l5_.id = :line AND e2_.out_of_plan = 0 AND e1_.updated_date >= :begin_date AND e1_.updated_date <= :end_date AND s6_.id = 38) AS E2

WHERE E1.id = E2.id";
            $params = array('engineer' => $user->getName(), 'project' => $project, 'line' => $line, 'begin_date' => $period_from, 'end_date' => $period_to);
            $em = $this->getDoctrine()->getManager();
            $stmt = $em->getConnection()->prepare($sql);
            $stmt->execute($params);
            $entities = $stmt->fetchAll();

I had to use native $sql although I'm not entirely comfortable with this I couldn't find a way to do it with Doctrine. Thanks to @Kapil 's answer.

Upvotes: 0

Debreczeni Andr&#225;s
Debreczeni Andr&#225;s

Reputation: 1678

Alternatively you can use Doctrine's DBAL as such: 

$query = "SELECT " ; // your query

// dbal connection from your controller
$conn = $this->container->get( 'doctrine.dbal.default_connection' );

// dbal connection from your entity repository
$conn = $this->getEntityManager()->getConnection();

$results = $conn->fetchAssoc( $query );

Upvotes: 1

Kapil
Kapil

Reputation: 896

Doctrine 2 doesn't support complex queries with createQueryBuilder. You have to use native query with ResultSetMapping. createQueryBuilder convert all the result in object that causes issue. To avoid any issue try native query.

See the below link for reference . http://doctrine-orm.readthedocs.org/en/latest/reference/native-sql.html

Upvotes: 1

Related Questions