Haven't been able to find a solid solution for this, but I have a mySQL query that I want to translate to Doctrine. It is a select from a subquery with joins and I might have read somewhere that joins are not allowed in subqueries in Doctrine.
Here is the SQL:
SELECT part, SUM(qty) as qty FROM (SELECT part, SUM(qty) as qty FROM sub LEFT JOIN main ON main.id = main_id WHERE hold != 1 GROUP BY name, part) AS tbl GROUP BY part
This is what I tried and it is all wrong.
$em = $this->getDoctrine()->getManager();
$q = $em->createQuery('v');
$q2 = $em->createSubQuery()
->select('m.part, sum(s.qty) qty')
->from('Sub s')
->leftJoin('s.main m')
->where('s.hold != 1')
->groupBy('m.part');
$q->select('m.part, sum(qty)', $q2->getDQL());
One of the first error I got was:
FatalErrorException: Error: Call to undefined method Doctrine\ORM\EntityManager::createSubQuery() in ....Controller.php line 238
I am pretty sure it isn't just this that I'm doing wrong but it is the first thing that's coming up. So getManager() apparently doesn't have a createSubQuery() function? What is the right way to do this?
