开发者

Subqueries in Doctrine for Having field

开发者 https://www.devze.com 2023-01-27 12:53 出处：网络
I am dealing with several bus routes. I want to fetch the two points closest to my departure and destination points.
相关专题：doctrinedoctrine-1.2symfony1

I am dealing with several bus routes. I want to fetch the two points closest to my departure and destination points. So what I do is :

$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)
        -&开发者_StackOverflow社区gt;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();

The error is :

Doctrine_Exception
Couldn't find class p4

Is there a better way to do this ? I thought that MIN should go with GROUP BY, and I tried GROUP BY p3.id and GROUP BY p4.id, but that didn't change.

For those it could help, here's the stack trace from Symfony : It's weird that the parseAggregateFunction() function erases the query, isn't it ?

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 ...

You might try something like 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.' 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();
0

上一篇:

:下一篇

更多 问答 相关资讯：

精彩评论

暂无评论...
登录 注册
Qedev.com
验证码 换一张
取 消

关注公众号

热门标签

图文推荐

Delphi - Custom drawing a message list

Delphi - Custom drawing a message list
C++ header-only include pattern

C++ header-only include pattern
IE7 Margin Collapses Into Padding

IE7 Margin Collapses Into Padding
in CoffeeScript, how can I use a variable as a key in a hash?

in CoffeeScript, how can I use a variable as a key in a hash?
Interactive visualization of a graph in python [closed]

Interactive visualization of a graph in python [closed]
How to customise PHP MYSQL tables?

How to customise PHP MYSQL tables?
High quality, simple random password generator

High quality, simple random password generator
Image Recognition ApI in android

Image Recognition ApI in android

开发者开发者网给大家分享系统运维,大数据运维,云计算,编程开发技巧,路由交换,运维和开发相关的资讯及技术文章，同时StackOverflow中文社区，知识经验交流分享。

法律声明：本站内容均为网友上传，网站举办方负责审核和监督，如存在版权或非法内容，欢迎举报，我们将尽快予以删除。邮箱：devze@qq.com

Copyright © 2018-2020 开发者. All rights reserved. Powered by 开发者ICP备案号: 京ICP备10032868号-9