mysql - Doctrine 中用于 Having 字段的子查询

Question

我正在处理几条公交路线。我想获取离我的出发点和目的地点最近的两个点。所以我所做的是:

$q =    Doctrine_query::create()
        ->select('r.*')
//d1 and d2 are the distance between the p1 and p2 points and my dest and 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 is just to select only the points close enough to my depa and 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 are to fetch the two closest points, and they seem to be the cause of the 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.')))))
            FROM Point p3
            WHERE p3.lat >= '.$depa_lat - $eps_lat.' AND p3.lat <= '.$depa_lat + $eps_lat.' AND p3.lng >= '.$depa_lng - $eps_lng.' AND p3.lng <= '.$depa_lng + $eps_lng.' AND 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.')))))
            FROM Point p4
            WHERE p4.lat >= '.$dest_lat - $eps_lat.' AND p4.lat <= '.$dest_lat + $eps_lat.' AND p4.lng >= '.$dest_lng - $eps_lng.' AND p4.lng <= '.$dest_lng + $eps_lng.' AND p4.route_id = p2.route_id
        )')
        ->orderBy('d1+d2')
        ->execute();

错误是:

Doctrine_Exception
Couldn't find class p4

有更好的方法吗？我认为 MIN 应该与 GROUP BY 一起使用，并且我尝试了 GROUP BY p3.id 和 GROUP BY p4.id，但这并没有改变。

对于那些可能有所帮助的人，这里是 Symfony 的堆栈跟踪:parseAggregateFunction() 函数删除查询很奇怪，不是吗？

at Doctrine_Table->initDefinition()
in SF_SYMFONY_LIB_DIR/plugins/sfDoctrinePlugin/lib/vendor/doctrine/Doctrine/Table.php line 256 ...
at Doctrine_Table->__construct('-58', object('Doctrine_Connection_Mysql'), 1)
in SF_SYMFONY_LIB_DIR/plugins/sfDoctrinePlugin/lib/vendor/doctrine/Doctrine/Connection.php line 1126 ...
at Doctrine_Connection->getTable('-58')
in SF_SYMFONY_LIB_DIR/plugins/sfDoctrinePlugin/lib/vendor/doctrine/Doctrine/Query.php line 1942 ...
at Doctrine_Query->loadRoot('-58', '-58')
in SF_SYMFONY_LIB_DIR/plugins/sfDoctrinePlugin/lib/vendor/doctrine/Doctrine/Query.php line 1740 ...
at Doctrine_Query->load('-58', )
in SF_SYMFONY_LIB_DIR/plugins/sfDoctrinePlugin/lib/vendor/doctrine/Doctrine/Query/Having.php line 89 ...
at Doctrine_Query_Having->_parseAliases('-58.3819582))))) FRO')
in SF_SYMFONY_LIB_DIR/plugins/sfDoctrinePlugin/lib/vendor/doctrine/Doctrine/Query/Having.php line 70 ...
at Doctrine_Query_Having->parseAggregateFunction('-58.3819582))))) FRO')
in SF_SYMFONY_LIB_DIR/plugins/sfDoctrinePlugin/lib/vendor/doctrine/Doctrine/Query/Having.php line 63 ...
at 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 ...
at Doctrine_Query_Having->parseAggregateFunction('RADIANS(p4.lng) - RADIANS(-58.3819582))))) FROM P')
in SF_SYMFONY_LIB_DIR/plugins/sfDoctrinePlugin/lib/vendor/doctrine/Doctrine/Query/Having.php line 63 ...
at Doctrine_Query_Having->parseAggregateFunction('p4.lat)) * COS(RADIANS(p4.lng) - RADIANS(-58.3819582))))) FROM Po')
in SF_SYMFONY_LIB_DIR/plugins/sfDoctrinePlugin/lib/vendor/doctrine/Doctrine/Query/Having.php line 63 ...
at Doctrine_Query_Having->parseAggregateFunction('RADIANS(p4.lat)) * COS(RADIANS(p4.lng) - RADIANS(-58.3819582))))) FROM Poi')
in SF_SYMFONY_LIB_DIR/plugins/sfDoctrinePlugin/lib/vendor/doctrine/Doctrine/Query/Having.php line 63 ...
at Doctrine_Query_Having->parseAggregateFunction('-58.3819582)) * COS(RADIANS(p4.lat)) * COS(RADIANS(p4.lng) - RADIANS(-58.3819582))))) FROM Poin')
in SF_SYMFONY_LIB_DIR/plugins/sfDoctrinePlugin/lib/vendor/doctrine/Doctrine/Query/Having.php line 63 ...
at Doctrine_Query_Having->parseAggregateFunction('RADIANS(-58.3819582)) * COS(RADIANS(p4.lat)) * COS(RADIANS(p4.lng) - RADIANS(-58.3819582))))) FROM Point')
in SF_SYMFONY_LIB_DIR/plugins/sfDoctrinePlugin/lib/vendor/doctrine/Doctrine/Query/Having.php line 63 ...
at Doctrine_Query_Having->parseAggregateFunction('p4.lat)) + COS(RADIANS(-58.3819582)) * COS(RADIANS(p4.lat)) * COS(RADIANS(p4.lng) - RADIANS(-58.3819582))))) FROM Point p')
in SF_SYMFONY_LIB_DIR/plugins/sfDoctrinePlugin/lib/vendor/doctrine/Doctrine/Query/Having.php line 63 ...
at Doctrine_Query_Having->parseAggregateFunction('RADIANS(p4.lat)) + COS(RADIANS(-58.3819582)) * COS(RADIANS(p4.lat)) * COS(RADIANS(p4.lng) - RADIANS(-58.3819582))))) FROM Point p4')
in SF_SYMFONY_LIB_DIR/plugins/sfDoctrinePlugin/lib/vendor/doctrine/Doctrine/Query/Having.php line 63 ...
at 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))))) FROM Point p4 W')
in SF_SYMFONY_LIB_DIR/plugins/sfDoctrinePlugin/lib/vendor/doctrine/Doctrine/Query/Having.php line 63 ...
at 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))))) FROM Point p4 WH')
in SF_SYMFONY_LIB_DIR/plugins/sfDoctrinePlugin/lib/vendor/doctrine/Doctrine/Query/Having.php line 63 ...
at 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))))) FROM Point p4 WHE')
in SF_SYMFONY_LIB_DIR/plugins/sfDoctrinePlugin/lib/vendor/doctrine/Doctrine/Query/Having.php line 63 ...
at 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))))) FROM Point p4 WHER')
in SF_SYMFONY_LIB_DIR/plugins/sfDoctrinePlugin/lib/vendor/doctrine/Doctrine/Query/Having.php line 63 ...
at 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))))) FROM Point p4 WHERE')
in SF_SYMFONY_LIB_DIR/plugins/sfDoctrinePlugin/lib/vendor/doctrine/Doctrine/Query/Having.php line 63 ...
at 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))))) FROM Point p4 WHERE 0')
in SF_SYMFONY_LIB_DIR/plugins/sfDoctrinePlugin/lib/vendor/doctrine/Doctrine/Query/Having.php line 118 ...
at 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))))) FROM Point p4 WHERE 0')
in SF_SYMFONY_LIB_DIR/plugins/sfDoctrinePlugin/lib/vendor/doctrine/Doctrine/Query/Condition.php line 92 ...
at 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))))) FROM Point p4 WHERE 0')
in SF_SYMFONY_LIB_DIR/plugins/sfDoctrinePlugin/lib/vendor/doctrine/Doctrine/Query/Condition.php line 80 ...
at 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))))) FROM Point p4 WHERE 0 AND p4.route_id = p2.route_id')
in SF_SYMFONY_LIB_DIR/plugins/sfDoctrinePlugin/lib/vendor/doctrine/Doctrine/Query/Abstract.php line 2077 ...
at 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))))) FROM Point p4 WHERE 0 AND p4.route_id = p2.route_id'))
in SF_SYMFONY_LIB_DIR/plugins/sfDoctrinePlugin/lib/vendor/doctrine/Doctrine/Query.php line 1167 ...
at Doctrine_Query->buildSqlQuery(1)
in SF_SYMFONY_LIB_DIR/plugins/sfDoctrinePlugin/lib/vendor/doctrine/Doctrine/Query.php line 1133 ...
at Doctrine_Query->getSqlQuery(array())
in SF_SYMFONY_LIB_DIR/plugins/sfDoctrinePlugin/lib/vendor/doctrine/Doctrine/Query/Abstract.php line 958 ...
at Doctrine_Query_Abstract->_execute(array())
in SF_SYMFONY_LIB_DIR/plugins/sfDoctrinePlugin/lib/vendor/doctrine/Doctrine/Query/Abstract.php line 1026 ...
at Doctrine_Query_Abstract->execute()
in SF_ROOT_DIR/lib/model/doctrine/LineTable.class.php line 58 ...
at 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 ...
at mapActions->executeSearch(object('sfWebRequest'))
in SF_SYMFONY_LIB_DIR/action/sfActions.class.php line 60 ...
at sfActions->execute(object('sfWebRequest'))
in SF_SYMFONY_LIB_DIR/filter/sfExecutionFilter.class.php line 92 ...
at sfExecutionFilter->executeAction(object('mapActions'))
in SF_SYMFONY_LIB_DIR/filter/sfExecutionFilter.class.php line 78 ...
at sfExecutionFilter->handleAction(object('sfFilterChain'), object('mapActions'))
in SF_SYMFONY_LIB_DIR/filter/sfExecutionFilter.class.php line 42 ...
at sfExecutionFilter->execute(object('sfFilterChain'))
in SF_SYMFONY_LIB_DIR/filter/sfFilterChain.class.php line 53 ...
at sfFilterChain->execute()
in SF_SYMFONY_LIB_DIR/filter/sfRenderingFilter.class.php line 33 ...
at sfRenderingFilter->execute(object('sfFilterChain'))
in SF_SYMFONY_LIB_DIR/filter/sfFilterChain.class.php line 53 ...
at sfFilterChain->execute()
in SF_SYMFONY_LIB_DIR/controller/sfController.class.php line 238 ...
at sfController->forward('map', 'search')
in SF_SYMFONY_LIB_DIR/controller/sfFrontWebController.class.php line 48 ...
at sfFrontWebController->dispatch()
in SF_SYMFONY_LIB_DIR/util/sfContext.class.php line 170 ...
at sfContext->dispatch()
in SF_ROOT_DIR/web/frontend_dev.php line 13 ...

Answer 1

你可以尝试这样的事情:

$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.' AND p3.lat <= '.$depa_lat + $eps_lat.' AND p3.lng >= '.$depa_lng - $eps_lng.' AND p3.lng <= '.$depa_lng + $eps_lng.' AND 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.' AND p4.lat <= '.$dest_lat + $eps_lat.' AND p4.lng >= '.$dest_lng - $eps_lng.' AND p4.lng <= '.$dest_lng + $eps_lng.' AND p4.route_id = p2.route_id');

$q->having('d1 = (' . $d1->getDql() . ')')
  ->having('d2 = (' . $d2->getDql() . ')')
  ->orderBy('d1+d2')
  ->execute();