php - Get Max of AVG in Symfony 2.4 Doctrine -

i need max of list of avg in symfony doctrine

my doctrine query average follows :

select p,avg(p.pathtime) avgtime  shopperanalyticsentitybundle:path p   join p.shopper sh join p.floor f   join f.store s  p.floor=".$floorid." ".$filter."  group p.xpath, p.ypath 

to max of list of averages modified (after referring this):

select max(avgtime) maxtime  (select p,avg(p.pathtime) avgtime        shopperanalyticsentitybundle:path p         join p.shopper sh         join p.floor f         join f.store s        p.floor=".$floorid." ".$filter."        group p.xpath, p.ypath) 

i following error:

[semantical error] line 0, col 38 near '(select p,avg(p.pathtime)': error: class '(' not defined 

so switched native sql same result:

$maxsql   = "select max(t.avgtime) maxtime ( select avg(p.path_time) avgtime  path p join shopper sh join floor f join store s p.floor_id=".$floorid." ".$filter." group p.x_path, p.y_path ) t ";  $connection = $em->getconnection(); $statement = $connection->prepare($maxsql); $statement->execute(); $results = $statement->fetchall(); 

i result sql takes 23.4457 seconds run

can suggest easy way solve in doctrine.

you should use dotrine way :

$query = doctrine_query::create()   ->select('max(avgtime) maxtime ')   ->from('(select p,avg(p.pathtime) avgtime             shopperanalyticsentitybundle:path p             join p.shopper sh             join p.floor f             join f.store s             p.floor=".$floorid." ".$filter."             group p.xpath, p.ypath)         ')   ->execute(); 

how did yours ?