Error in nested SubQuery in DQL: class '(' not defined

Question

I have a DQL as shown below:

SELECT at, count(at.id) FROM AccountTriple at JOIN at.property p JOIN at.account ac WHERE p.create_analytics = '1' GROUP BY at.property, at.value, ac.service 

As you can see, it has three connections. Because "at" and "ac" have a lot of data. In an attempt to optimize it, I am trying to move the check "p.create_analytics = '1" before joining "ac" to give it a smaller dataset for the connection. I am trying to achieve something like this:

 SELECT at, count(at.id) FROM ( SELECT at FROM AccountTriple at JOIN at.property p WHERE p.create_analytics = '1' ) JOIN at.account ac GROUP BY at.property, at.value, ac.service 

But for some reason my syntax does not work. An error message will appear:

Semantic error] row 0, column 29 next to '(SELECT at FROM': Error: Class '(' undefined.

I did not find a similar example elsewhere. Can anyone help me fix this DQL query in order to work? Thanks in advance.

Answer 1

Use the createSubquery() function to create a subquery in the Doctrine. Then you can nest the subquery in your main query.

Example

 // build root query $query = Doctrine_Query::create() ->from('Movie m') ->where('name = ?', 'Prometheus') ; // build subquery $subquery = $query->createSubquery() ->from('SeenMovie sm') ->where('m.name = sm.name') ; // nest subquery and execute $query->where('EXISTS (' . $subquery->getDql() . ')')->execute(); 

additional literature
Brochure template for creating Doctrine subqueries of any complexity