mysql - Subqueries in Doctrine for Having field -
i dealing several bus routes. want fetch 2 points closest departure , destination points. :
$q = doctrine_query::create() ->select('r.*') //d1 , d2 distance between p1 , p2 points , dest , depa points. ->addselect("(6371 * acos(sin(radians($depa_lat)) * sin(radians(p1.lat)) + cos(radians($depa_lat)) * cos(radians(p1.lat)) * cos(radians(p1.lng) - radians($depa_lng)))) d1") ->addselect("(6371 * acos(sin(radians($dest_lat)) * sin(radians(p2.lat)) + cos(radians($dest_lat)) * cos(radians(p2.lat)) * cos(radians(p2.lng) - radians($dest_lng)))) d2") ->from('route r') ->innerjoin('r.points p1') ->innerjoin('r.points p2') //this select points close enough depa , dest point ->andwhere('p1.lat >= ?',$depa_lat - $eps_lat) ->andwhere('p1.lat <= ?',$depa_lat + $eps_lat) ->andwhere('p1.lng >= ?',$depa_lng - $eps_lng) ->andwhere('p1.lng <= ?',$depa_lng + $eps_lng) ->andwhere('p2.lat >= ?',$dest_lat - $eps_lat) ->andwhere('p2.lat <= ?',$dest_lat + $eps_lat) ->andwhere('p2.lng >= ?',$dest_lng - $eps_lng) ->andwhere('p2.lng <= ?',$dest_lng + $eps_lng) //those subqueries fetch 2 closest points, , seem cause of crash ->having('d1 = ( select min((6371 * acos(sin(radians('.$depa_lat.')) * sin(radians(p3.lat)) + cos(radians('.$depa_lat.')) * cos(radians(p3.lat)) * cos(radians(p3.lng) - radians('.$depa_lng.'))))) point p3 p3.lat >= '.$depa_lat - $eps_lat.' , p3.lat <= '.$depa_lat + $eps_lat.' , p3.lng >= '.$depa_lng - $eps_lng.' , p3.lng <= '.$depa_lng + $eps_lng.' , p3.route_id = p1.route_id )') ->having('d2 = ( select min((6371 * acos(sin(radians('.$dest_lat.')) * sin(radians(p4.lat)) + cos(radians('.$dest_lat.')) * cos(radians(p4.lat)) * cos(radians(p4.lng) - radians('.$dest_lng.'))))) point p4 p4.lat >= '.$dest_lat - $eps_lat.' , p4.lat <= '.$dest_lat + $eps_lat.' , p4.lng >= '.$dest_lng - $eps_lng.' , p4.lng <= '.$dest_lng + $eps_lng.' , p4.route_id = p2.route_id )') ->orderby('d1+d2') ->execute(); the error :
doctrine_exception couldn't find class p4 is there better way ? thought min should go group by, , tried group p3.id , group p4.id, didn't change.
for help, here's stack trace symfony : it's weird parseaggregatefunction() function erases query, isn't ?
at doctrine_table->initdefinition() in sf_symfony_lib_dir/plugins/sfdoctrineplugin/lib/vendor/doctrine/doctrine/table.php line 256 ... @ doctrine_table->__construct('-58', object('doctrine_connection_mysql'), 1) in sf_symfony_lib_dir/plugins/sfdoctrineplugin/lib/vendor/doctrine/doctrine/connection.php line 1126 ... @ doctrine_connection->gettable('-58') in sf_symfony_lib_dir/plugins/sfdoctrineplugin/lib/vendor/doctrine/doctrine/query.php line 1942 ... @ doctrine_query->loadroot('-58', '-58') in sf_symfony_lib_dir/plugins/sfdoctrineplugin/lib/vendor/doctrine/doctrine/query.php line 1740 ... @ doctrine_query->load('-58', ) in sf_symfony_lib_dir/plugins/sfdoctrineplugin/lib/vendor/doctrine/doctrine/query/having.php line 89 ... @ doctrine_query_having->_parsealiases('-58.3819582))))) fro') in sf_symfony_lib_dir/plugins/sfdoctrineplugin/lib/vendor/doctrine/doctrine/query/having.php line 70 ... @ doctrine_query_having->parseaggregatefunction('-58.3819582))))) fro') in sf_symfony_lib_dir/plugins/sfdoctrineplugin/lib/vendor/doctrine/doctrine/query/having.php line 63 ... @ doctrine_query_having->parseaggregatefunction('p4.lng) - radians(-58.3819582))))) from') in sf_symfony_lib_dir/plugins/sfdoctrineplugin/lib/vendor/doctrine/doctrine/query/having.php line 63 ... @ doctrine_query_having->parseaggregatefunction('radians(p4.lng) - radians(-58.3819582))))) p') in sf_symfony_lib_dir/plugins/sfdoctrineplugin/lib/vendor/doctrine/doctrine/query/having.php line 63 ... @ doctrine_query_having->parseaggregatefunction('p4.lat)) * cos(radians(p4.lng) - radians(-58.3819582))))) po') in sf_symfony_lib_dir/plugins/sfdoctrineplugin/lib/vendor/doctrine/doctrine/query/having.php line 63 ... @ doctrine_query_having->parseaggregatefunction('radians(p4.lat)) * cos(radians(p4.lng) - radians(-58.3819582))))) poi') in sf_symfony_lib_dir/plugins/sfdoctrineplugin/lib/vendor/doctrine/doctrine/query/having.php line 63 ... @ doctrine_query_having->parseaggregatefunction('-58.3819582)) * cos(radians(p4.lat)) * cos(radians(p4.lng) - radians(-58.3819582))))) poin') in sf_symfony_lib_dir/plugins/sfdoctrineplugin/lib/vendor/doctrine/doctrine/query/having.php line 63 ... @ doctrine_query_having->parseaggregatefunction('radians(-58.3819582)) * cos(radians(p4.lat)) * cos(radians(p4.lng) - radians(-58.3819582))))) point') in sf_symfony_lib_dir/plugins/sfdoctrineplugin/lib/vendor/doctrine/doctrine/query/having.php line 63 ... @ doctrine_query_having->parseaggregatefunction('p4.lat)) + cos(radians(-58.3819582)) * cos(radians(p4.lat)) * cos(radians(p4.lng) - radians(-58.3819582))))) point p') in sf_symfony_lib_dir/plugins/sfdoctrineplugin/lib/vendor/doctrine/doctrine/query/having.php line 63 ... @ doctrine_query_having->parseaggregatefunction('radians(p4.lat)) + cos(radians(-58.3819582)) * cos(radians(p4.lat)) * cos(radians(p4.lng) - radians(-58.3819582))))) point p4') in sf_symfony_lib_dir/plugins/sfdoctrineplugin/lib/vendor/doctrine/doctrine/query/having.php line 63 ... @ doctrine_query_having->parseaggregatefunction('-58.3819582)) * sin(radians(p4.lat)) + cos(radians(-58.3819582)) * cos(radians(p4.lat)) * cos(radians(p4.lng) - radians(-58.3819582))))) point p4 w') in sf_symfony_lib_dir/plugins/sfdoctrineplugin/lib/vendor/doctrine/doctrine/query/having.php line 63 ... @ doctrine_query_having->parseaggregatefunction('radians(-58.3819582)) * sin(radians(p4.lat)) + cos(radians(-58.3819582)) * cos(radians(p4.lat)) * cos(radians(p4.lng) - radians(-58.3819582))))) point p4 wh') in sf_symfony_lib_dir/plugins/sfdoctrineplugin/lib/vendor/doctrine/doctrine/query/having.php line 63 ... @ doctrine_query_having->parseaggregatefunction('sin(radians(-58.3819582)) * sin(radians(p4.lat)) + cos(radians(-58.3819582)) * cos(radians(p4.lat)) * cos(radians(p4.lng) - radians(-58.3819582))))) point p4 whe') in sf_symfony_lib_dir/plugins/sfdoctrineplugin/lib/vendor/doctrine/doctrine/query/having.php line 63 ... @ doctrine_query_having->parseaggregatefunction('6371 * acos(sin(radians(-58.3819582)) * sin(radians(p4.lat)) + cos(radians(-58.3819582)) * cos(radians(p4.lat)) * cos(radians(p4.lng) - radians(-58.3819582))))) point p4 wher') in sf_symfony_lib_dir/plugins/sfdoctrineplugin/lib/vendor/doctrine/doctrine/query/having.php line 63 ... @ doctrine_query_having->parseaggregatefunction('(6371 * acos(sin(radians(-58.3819582)) * sin(radians(p4.lat)) + cos(radians(-58.3819582)) * cos(radians(p4.lat)) * cos(radians(p4.lng) - radians(-58.3819582))))) point p4 where') in sf_symfony_lib_dir/plugins/sfdoctrineplugin/lib/vendor/doctrine/doctrine/query/having.php line 63 ... @ doctrine_query_having->parseaggregatefunction('select min((6371 * acos(sin(radians(-58.3819582)) * sin(radians(p4.lat)) + cos(radians(-58.3819582)) * cos(radians(p4.lat)) * cos(radians(p4.lng) - radians(-58.3819582))))) point p4 0') in sf_symfony_lib_dir/plugins/sfdoctrineplugin/lib/vendor/doctrine/doctrine/query/having.php line 118 ... @ doctrine_query_having->load('d2 = select min((6371 * acos(sin(radians(-58.3819582)) * sin(radians(p4.lat)) + cos(radians(-58.3819582)) * cos(radians(p4.lat)) * cos(radians(p4.lng) - radians(-58.3819582))))) point p4 0') in sf_symfony_lib_dir/plugins/sfdoctrineplugin/lib/vendor/doctrine/doctrine/query/condition.php line 92 ... @ doctrine_query_condition->parse('d2 = select min((6371 * acos(sin(radians(-58.3819582)) * sin(radians(p4.lat)) + cos(radians(-58.3819582)) * cos(radians(p4.lat)) * cos(radians(p4.lng) - radians(-58.3819582))))) point p4 0') in sf_symfony_lib_dir/plugins/sfdoctrineplugin/lib/vendor/doctrine/doctrine/query/condition.php line 80 ... @ doctrine_query_condition->parse('d2 = select min((6371 * acos(sin(radians(-58.3819582)) * sin(radians(p4.lat)) + cos(radians(-58.3819582)) * cos(radians(p4.lat)) * cos(radians(p4.lng) - radians(-58.3819582))))) point p4 0 , p4.route_id = p2.route_id') in sf_symfony_lib_dir/plugins/sfdoctrineplugin/lib/vendor/doctrine/doctrine/query/abstract.php line 2077 ... @ doctrine_query_abstract->_processdqlquerypart('having', array('d2 = select min((6371 * acos(sin(radians(-58.3819582)) * sin(radians(p4.lat)) + cos(radians(-58.3819582)) * cos(radians(p4.lat)) * cos(radians(p4.lng) - radians(-58.3819582))))) point p4 0 , p4.route_id = p2.route_id')) in sf_symfony_lib_dir/plugins/sfdoctrineplugin/lib/vendor/doctrine/doctrine/query.php line 1167 ... @ doctrine_query->buildsqlquery(1) in sf_symfony_lib_dir/plugins/sfdoctrineplugin/lib/vendor/doctrine/doctrine/query.php line 1133 ... @ doctrine_query->getsqlquery(array()) in sf_symfony_lib_dir/plugins/sfdoctrineplugin/lib/vendor/doctrine/doctrine/query/abstract.php line 958 ... @ doctrine_query_abstract->_execute(array()) in sf_symfony_lib_dir/plugins/sfdoctrineplugin/lib/vendor/doctrine/doctrine/query/abstract.php line 1026 ... @ doctrine_query_abstract->execute() in sf_root_dir/lib/model/doctrine/linetable.class.php line 58 ... @ linetable->findlinesfromlatlngtolatlng('-34.5835397', '-58.4246329', '-58.3819582', '-58.3819582', 0.4) in sf_root_dir/apps/frontend/modules/map/actions/actions.class.php line 57 ... @ mapactions->executesearch(object('sfwebrequest')) in sf_symfony_lib_dir/action/sfactions.class.php line 60 ... @ sfactions->execute(object('sfwebrequest')) in sf_symfony_lib_dir/filter/sfexecutionfilter.class.php line 92 ... @ sfexecutionfilter->executeaction(object('mapactions')) in sf_symfony_lib_dir/filter/sfexecutionfilter.class.php line 78 ... @ sfexecutionfilter->handleaction(object('sffilterchain'), object('mapactions')) in sf_symfony_lib_dir/filter/sfexecutionfilter.class.php line 42 ... @ sfexecutionfilter->execute(object('sffilterchain')) in sf_symfony_lib_dir/filter/sffilterchain.class.php line 53 ... @ sffilterchain->execute() in sf_symfony_lib_dir/filter/sfrenderingfilter.class.php line 33 ... @ sfrenderingfilter->execute(object('sffilterchain')) in sf_symfony_lib_dir/filter/sffilterchain.class.php line 53 ... @ sffilterchain->execute() in sf_symfony_lib_dir/controller/sfcontroller.class.php line 238 ... @ sfcontroller->forward('map', 'search') in sf_symfony_lib_dir/controller/sffrontwebcontroller.class.php line 48 ... @ sffrontwebcontroller->dispatch() in sf_symfony_lib_dir/util/sfcontext.class.php line 170 ... @ sfcontext->dispatch() in sf_root_dir/web/frontend_dev.php line 13 ...
you might try this:
$q = doctrine_query::create() ->select('r.*') ->addselect("(6371 * acos(sin(radians($depa_lat)) * sin(radians(p1.lat)) + cos(radians($depa_lat)) * cos(radians(p1.lat)) * cos(radians(p1.lng) - radians($depa_lng)))) d1") ->addselect("(6371 * acos(sin(radians($dest_lat)) * sin(radians(p2.lat)) + cos(radians($dest_lat)) * cos(radians(p2.lat)) * cos(radians(p2.lng) - radians($dest_lng)))) d2") ->from('route r') ->innerjoin('r.points p1') ->innerjoin('r.points p2') ->andwhere('p1.lat >= ?',$depa_lat - $eps_lat) ->andwhere('p1.lat <= ?',$depa_lat + $eps_lat) ->andwhere('p1.lng >= ?',$depa_lng - $eps_lng) ->andwhere('p1.lng <= ?',$depa_lng + $eps_lng) ->andwhere('p2.lat >= ?',$dest_lat - $eps_lat) ->andwhere('p2.lat <= ?',$dest_lat + $eps_lat) ->andwhere('p2.lng >= ?',$dest_lng - $eps_lng) ->andwhere('p2.lng <= ?',$dest_lng + $eps_lng); $d1 = $q->createsubquery() ->select('min((6371 * acos(sin(radians('.$depa_lat.')) * sin(radians(p3.lat)) + cos(radians('.$depa_lat.')) * cos(radians(p3.lat)) * cos(radians(p3.lng) - radians('.$depa_lng.')))))') ->from('point p3') ->where('where p3.lat >= '.$depa_lat - $eps_lat.' , p3.lat <= '.$depa_lat + $eps_lat.' , p3.lng >= '.$depa_lng - $eps_lng.' , p3.lng <= '.$depa_lng + $eps_lng.' , p3.route_id = p1.route_id'); $d2 = $q->createsubquery() ->select('min((6371 * acos(sin(radians('.$dest_lat.')) * sin(radians(p4.lat)) + cos(radians('.$dest_lat.')) * cos(radians(p4.lat)) * cos(radians(p4.lng) - radians('.$dest_lng.')))))') ->from('point p4') ->where('p4.lat >= '.$dest_lat - $eps_lat.' , p4.lat <= '.$dest_lat + $eps_lat.' , p4.lng >= '.$dest_lng - $eps_lng.' , p4.lng <= '.$dest_lng + $eps_lng.' , p4.route_id = p2.route_id'); $q->having('d1 = (' . $d1->getdql() . ')') ->having('d2 = (' . $d2->getdql() . ')') ->orderby('d1+d2') ->execute();
Comments
Post a Comment