Symfony 1.4为1个查询提供15个DB请求 [英] Symfony 1.4 makes 15 DB requests for 1 query

问题描述

可能重复：

我目前对symfony应用程序有一个很大的问题，我有一个复杂的sql查询，使数据库连接太多。

查询顺序，搜索关键字使用doctrine可搜索，并检查地理位置。

这里完整的模型粘贴和sql日志，任何人都知道我做错了什么？ ：$ / $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $
{

// CHANGE ORDER
if（！$ orderby）{
$ orderby =a.created_at DESC;
} else if（$ orderby ==price）{
$ orderby =a.price ASC;
} else if（$ orderby ==date）{
$ orderby =a.created_at DESC;
} else {
$ orderby =a.created_at DESC;
}

//搜索表中的关键字
if（$ motscles）{
$ searchItem = Doctrine_Core :: getTable（'csw_Article'）;
$ results = $ searchItem-> search（$ motscles）;
$ ids = array（）;

foreach（$ results as $ result）{

$ ids [] = $ result ['id'];
}
if（sizeof（$ ids）== 0）{
$ ids [] = 0;
}
}
$ q = Doctrine_Core :: getTable（'csw_Article'）
- > createQuery（a）
- > leftJoin（'a .csw_CategorieArticle ca'）;

$ sfContext = sfContext :: getInstance（） - > getUser（）;

if（$ useDistance）{
$ lat =（string）（$ sfContext-> getAttribute（'userLat'））？ $ sfContext-> getAttribute（'userLat'）：sfConfig :: get（app_user_lat）;
$ long =（string）（$ sfContext-> getAttribute（'userLong'））？ $ sfContext-> getAttribute（'userLong'）：sfConfig :: get（app_user_long）;
$ radius = 18;
$ q-> select（a.longitude，a.latitude，（3959 * acos（cos（radians（'。$ lat。））* cos（弧度（纬度））*弧度（经度） - 弧度（'。$ long。））+ sin（弧度（'。$ lat。））* sin（弧度（纬度））））AS距离）; （距离，半径）;
$ q->
}
if（$ orderby ==distance）{
$ q-> orderBy（distance desc）;
}
$ q-> addOrderBy（$ orderby）;

if（$ catID）{
$ q-> where（'ca.categorie_id =？'，$ catID）;
}
如果（$ budget！= 0）{

$ budget_min = $ budget - （$ budget * 0.20）;
$ budget_max = $ budget +（$ budget * 0.20）;
$ q-> addwhere（'a.price>？'，$ budget_min）
- > addwhere（'a.price<？'，$ budget_max）;
}
if（$ userid）{
$ q-> addwhere（'a.userid =？'，$ userid）;
}


if（$ motscles）{
$ q-> whereIn（'a.id'，$ ids）;

}
$ q-> execute（）;
return $ q;
}

Sql日志

 ＃
 
 SELECT c.id AS c__id，c.longitude AS c__longitude，c.latitude AS c__latitude，（3959 * ACOS（COS（弧度（'45 .854719' ）* COS（弧度（c.latitude））* COS（弧度（c.longitude） - 弧度（' -  73.23246'））+ SIN（弧度（'45 .854719'））* SIN（弧度（c.latitude）） ））AS c__0 FROM csw__article c LEFT JOIN csw__categorie_article c2 ON c.id = c2.article_id WHERE（c2.categorie_id ='1'）HAVING c__0 < '18'ORDER BY c.created_at DESC 
 0.00s，doctrine连接
＃
 
 SELECT COUNT（*）AS num_results FROM（SELECT c.id，（3959 * ACOS（COS（弧度（'45 .854719'））* COS（弧度（c.latitude））* COS（弧度（c.longitude） - 弧度（' -  73.23246'））+ SIN（弧度（'45 .854719'））* SIN（弧度（c.latitude））））AS c__0 FROM csw__article c LEFT JOIN csw__categorie_article c2 ON c.id = c2.article_id WHERE c2.categorie_id ='1'GROUP BY c.id HAVING c__0<'18'）dctrn_count_query 
 0.00s，doctrine连接
＃
 
 SELECT DISTINCT c3.id，（3959 * ACOS（COS（弧度（'45 .854719'））* COS（弧度（c3 ）。* COS（弧度（c3.longitude） - 弧度（' -  73.23246'））+ SIN（弧度（'45 .854719'））* SIN（弧度（c3.latitude））））AS c3__0从csw__article c3 LEFT JOIN csw__categorie_article c4 ON c3.id = c4.article_id WHERE c4.categorie_id ='1'HAVING c3__0< '18'ORDER BY c3.created_at DESC LIMIT 10 
 0.00s，doctrine连接
＃
 
 SELECT c.id AS c__id，c.longitude AS c__longitude，c。 （3959 * ACOS（COS（弧度（'45 .854719'））* COS（弧度（弧度））* COS（弧度（c.longitude） - 弧度（' -  73.23246'））+ SIN（弧度（'45 .854719'））* SIN（弧度（c.latitude））））AS c__0 FROM csw__article c LEFT JOIN csw__categorie_article c2 ON c.id = c2.article_id WHERE c.id IN（'1'，'20'，' 11，2，21，12，3，13，4，14）AND（c2.categorie_id ='1'）HAVING c__0 < '18'ORDER BY c.created_at DESC 
 0.00s，doctrine连接
＃
 
 SELECT c.id AS c__id，c.userid AS c__userid，c.title AS c__title，c.city AS c__city，c.description AS c__description，c.image AS c__image，c.price AS c__price，c.featured AS c__featured，c.state AS c__state，c.zipcode AS c__zipcode，c.is_public AS c__is_public， c.is_activated AS c__is_activated，c.expires_at AS c__expires_at，c.created_at AS c__created_at，c.updated_at AS c__updated_at，c.latitude AS c__latitude，c.longitude AS c__longitude FROM csw__article c WHERE（c.id ='1'）LIMIT 1 
 0.00s，doctrine连接
＃
 
 SELECT c.id AS c__id，c.userid AS c__userid，c.title AS c__title，c.city AS c__city，c .description AS c__description，c.image AS c__image，c.price AS c__price，c.featured AS c__featured，c.state AS c__state，c.zipcode AS c__zipcode，c.is_public AS c__is_public，c.is_activated AS c__is_activated，c.expires_at AS c__expires_at，c.created_at AS c__created_at，c.upd ated_at AS c__updated_at，c.latitude AS c__latitude，c.longitude AS c__longitude FROM csw__article c WHERE（c.id ='20'）LIMIT 1 
 0.00s，doctrine连接
＃
 
 SELECT c.id AS c__id，c.userid AS c__userid，c.title AS c__title，c.city AS c__city，c.description AS c__description，c.image AS c__image，c.price AS c__price，c.featured AS c__featured，c.state AS c__state，c.zipcode AS c__zipcode，c.is_public AS c__is_public，c.is_activated AS c__is_activated，c.expires_at AS c__expires_at，c.created_at AS c__created_at，c.updated_at AS c__updated_at，c.latitude AS c__latitude ，c.longitude AS c__longitude FROM csw__article c WHERE（c.id ='14'）LIMIT 1 
 0.00s，doctrine连接
＃
 
 SELECT c.id AS c__id，c.userid AS c__userid，c.title AS c__title，c.city AS c__city，c.description AS c__description，c.image AS c__image，c.price AS c__price，c.featured AS c__featured，c.state AS c__state， c.zipcode AS c__zipcode，c.is_public AS c__is_public，c.is _activated AS c__is_activated，c.expires_at AS c__expires_at，c.created_at AS c__created_at，c.updated_at AS c__updated_at，c.latitude AS c__latitude，c.longitude AS c__longitude FROM csw__article c WHERE（c.id ='13'）LIMIT 1 
 0.00s，doctrine连接
＃
 
 SELECT c.id AS c__id，c.userid AS c__userid，c.title AS c__title，c.city AS c__city，c.description AS c_description，c.image AS c__image，c.price AS c__price，c.featured AS c__featured，c.state AS c__state，c.zipcode AS c__zipcode，c.is_public AS c__is_public，c.is_activated AS c__is_activated，c.expires_at AS c__expires_at ，c.created_at AS c__created_at，c.updated_at AS c__updated_at，c.latitude AS c__latitude，c.longitude AS c__longitude FROM csw__article c WHERE（c.id ='12'）LIMIT 1 
 0.00s，doctrine连接
＃
 
 SELECT c.id AS c__id，c.userid AS c__userid，c.title AS c__title，c.city AS c__city，c.description AS c__description，c.image AS c__image， c.price AS c__price，c.featur ed AS c__featured，c.state AS c__state，c.zipcode AS c__zipcode，c.is_public AS c__is_public，c.is_activated AS c__is_activated，c.expires_at AS c__expires_at，c.created_at AS c__created_at，c.updated_at AS c__updated_at，c.latitude AS c__latitude，c.longitude AS c__longitude FROM csw__article c WHERE（c.id ='11'）LIMIT 1 
 0.00s，doctrine连接
＃
 
 SELECT c.id AS c__id，c.userid AS c__userid，c.title AS c__title，c.city AS c__city，c.description AS c__description，c.image AS c__image，c.price AS c__price，c.featured AS c__featured，c.state AS c__state ，c.zipcode AS c__zipcode，c.is_public AS c__is_public，c.is_activated AS c__is_activated，c.expires_at AS c__expires_at，c.created_at AS c__created_at，c.updated_at AS c__updated_at，c.latitude AS c__latitude，c.longitude AS c__longitude FROM csw__art c WHERE（c.id ='4'）LIMIT 1 
 0.00s，doctrine连接
＃
 
 SELECT c.id AS c__id，c.userid AS c__userid， c.title AS c__title，c.cit y AS c__city，c.description AS c__description，c.image AS c__image，c.price AS c__price，c.featured AS c__featured，c.state AS c__state，c.zipcode AS c__zipcode，c.is_public AS c__is_public，c.is_activated AS c__is_activated，c.expires_at AS c__expires_at，c.created_at AS c__created_at，c.updated_at AS c__updated_at，c.latitude AS c__latitude，c.longitude AS c__longitude FROM csw__article c WHERE（c.id ='3'）LIMIT 1 
 0.00s，doctrine连接
＃
 
 SELECT c.id AS c__id，c.userid AS c__userid，c.title AS c__title，c.city AS c__city，c.description AS c _description ，c.image AS c__image，c.price AS c__price，c.featured AS c__featured，c.state AS c__state，c.zipcode AS c__zipcode，c.is_public AS c__is_public，c.is_activated AS c__is_activated，c.expires_at AS c__expires_at，c .created_at AS c__created_at，c.updated_at AS c__updated_at，c.latitude AS c__latitude，c.longitude AS c__longitude FROM csw__article c WHERE（c.id ='2'）LIMIT 1 
 0.00s，doctrineconn ection 
＃
 
 SELECT c.id AS c__id，c.userid AS c__userid，c.title AS c__title，c.city AS c__city，c.description AS c _description，c.image AS c__image ，c.price AS c__price，c.featured AS c__featured，c.state AS c__state，c.zipcode AS c__zipcode，c.is_public AS c__is_public，c.is_activated AS c__is_activated，c.expires_at AS c__expires_at，c.created_at AS c__created_at，c （c.id ='21'）LIMIT 1 
 0.00s，doctrine连接
＃
 
 SELECT c.id AS c__id，c.sluggish AS c__sluggish，c.name AS c__name，c.parent_id AS c__parent_id，c.lft AS c__lft，c.rgt AS c__rgt，c.level AS c__level FROM csw__categorie c WHERE（c.parent_id ='1'）ORDER BY c.parent_id ASC，c.lft ASC 
 0.00s，doctrine连接
＃
 
 SELECT s.id AS s__id，s.first_name AS s__first_name，s.last_name AS s__last_name，s.email_address AS s__email_address，s.username AS s_ _username，s.algorithm AS s__algorithm，s.salt AS s__salt，s.password AS s__password，s.is_active AS s__is_active，s.is_super_admin AS s__is_super_admin，s.last_login AS s__last_login，s.created_at AS s__created_at，s.updated_at AS s__updated_at FROM sf_guard_user s WHERE（s.id ='5'）LIMIT 1 
  

解决方案

您可以启用xdebug_loggin来查找您的查询来源：

  // factoryories.yml 
 dev ：
 logger：
 param：
 loggers：
 sf_web_debug：
 param：
 xdebug_logging：true 
  / pre> 
 
 
这将记录跟踪到每个查询，并应该给你一个线索出了什么问题
 

  
Possible Duplicate:

  got a select that does 10 query in doctrine (Symfony)  
I currently have a big problem with my symfony app, I have a complexe sql query that makes too much connections the the database.

The query order, search for keyword using doctrine searchable, and check the geo position. 

Here the complete model pasted and the sql log, anyone got an idea what I am doing wrong ? :/
public function getListItems($orderby, $budget, $motscles, $userid, $catID, $useDistance = true)
{

   // CHANGE ORDER 
   if(!$orderby){
     $orderby = "a.created_at DESC";
   }else if($orderby == "price"){
     $orderby = "a.price ASC";
   }else if($orderby == "date") {
     $orderby = "a.created_at DESC";
    }else{
     $orderby = "a.created_at DESC";
   }

   // Search Keywords in table
   if($motscles){
    $searchItem = Doctrine_Core::getTable('csw_Article');
    $results = $searchItem->search($motscles);
    $ids = array();

    foreach ($results as $result) {

        $ids[] = $result['id'];
    }
    if(sizeof($ids) == 0){
      $ids[] = 0;
    }
   }        
    $q = Doctrine_Core::getTable('csw_Article')
        ->createQuery("a")
        ->leftJoin('a.csw_CategorieArticle ca');

    $sfContext = sfContext::getInstance()->getUser();

    if($useDistance){
        $lat = (string)($sfContext->getAttribute('userLat')) ? $sfContext->getAttribute('userLat') : sfConfig::get("app_user_lat");
       $long = (string)($sfContext->getAttribute('userLong')) ? $sfContext->getAttribute('userLong') : sfConfig::get("app_user_long");  
        $radius = 18;
        $q->select("a.longitude, a.latitude, (3959 * acos(cos(radians('".$lat."')) * cos(radians(latitude)) * cos(radians(longitude) - radians('".$long."')) + sin(radians('".$lat."')) * sin(radians(latitude)))) AS distance");
        $q->having("distance < ?", $radius);
    }
    if($orderby == "distance") {            
        $q->orderBy("distance desc");   
    }               
        $q->addOrderBy($orderby);

     if($catID){
       $q->where('ca.categorie_id = ?', $catID);
     }
     if($budget != 0){

      $budget_min = $budget - ($budget * 0.20);
      $budget_max = $budget + ($budget * 0.20);
      $q->addwhere('a.price > ?',$budget_min)
        ->addwhere('a.price < ?',$budget_max);
     }  
     if($userid){
       $q->addwhere('a.userid = ?', $userid);
     }


     if($motscles){
       $q->whereIn('a.id', $ids);

     }        
     $q->execute();      
     return $q;
}
Sql log
#

SELECT c.id AS c__id, c.longitude AS c__longitude, c.latitude AS c__latitude, (3959 * ACOS(COS(radians('45.854719')) * COS(radians(c.latitude)) * COS(radians(c.longitude) - radians('-73.23246')) + SIN(radians('45.854719')) * SIN(radians(c.latitude)))) AS c__0 FROM csw__article c LEFT JOIN csw__categorie_article c2 ON c.id = c2.article_id WHERE (c2.categorie_id = '1') HAVING c__0 < '18' ORDER BY c.created_at DESC
0.00s, "doctrine" connection
#

SELECT COUNT(*) AS num_results FROM (SELECT c.id, (3959 * ACOS(COS(radians('45.854719')) * COS(radians(c.latitude)) * COS(radians(c.longitude) - radians('-73.23246')) + SIN(radians('45.854719')) * SIN(radians(c.latitude)))) AS c__0 FROM csw__article c LEFT JOIN csw__categorie_article c2 ON c.id = c2.article_id WHERE c2.categorie_id = '1' GROUP BY c.id HAVING c__0 < '18') dctrn_count_query
0.00s, "doctrine" connection
#

SELECT DISTINCT c3.id, (3959 * ACOS(COS(radians('45.854719')) * COS(radians(c3.latitude)) * COS(radians(c3.longitude) - radians('-73.23246')) + SIN(radians('45.854719')) * SIN(radians(c3.latitude)))) AS c3__0 FROM csw__article c3 LEFT JOIN csw__categorie_article c4 ON c3.id = c4.article_id WHERE c4.categorie_id = '1' HAVING c3__0 < '18' ORDER BY c3.created_at DESC LIMIT 10
0.00s, "doctrine" connection
#

SELECT c.id AS c__id, c.longitude AS c__longitude, c.latitude AS c__latitude, (3959 * ACOS(COS(radians('45.854719')) * COS(radians(c.latitude)) * COS(radians(c.longitude) - radians('-73.23246')) + SIN(radians('45.854719')) * SIN(radians(c.latitude)))) AS c__0 FROM csw__article c LEFT JOIN csw__categorie_article c2 ON c.id = c2.article_id WHERE c.id IN ('1', '20', '11', '2', '21', '12', '3', '13', '4', '14') AND (c2.categorie_id = '1') HAVING c__0 < '18' ORDER BY c.created_at DESC
0.00s, "doctrine" connection
#

SELECT c.id AS c__id, c.userid AS c__userid, c.title AS c__title, c.city AS c__city, c.description AS c__description, c.image AS c__image, c.price AS c__price, c.featured AS c__featured, c.state AS c__state, c.zipcode AS c__zipcode, c.is_public AS c__is_public, c.is_activated AS c__is_activated, c.expires_at AS c__expires_at, c.created_at AS c__created_at, c.updated_at AS c__updated_at, c.latitude AS c__latitude, c.longitude AS c__longitude FROM csw__article c WHERE (c.id = '1') LIMIT 1
0.00s, "doctrine" connection
#

SELECT c.id AS c__id, c.userid AS c__userid, c.title AS c__title, c.city AS c__city, c.description AS c__description, c.image AS c__image, c.price AS c__price, c.featured AS c__featured, c.state AS c__state, c.zipcode AS c__zipcode, c.is_public AS c__is_public, c.is_activated AS c__is_activated, c.expires_at AS c__expires_at, c.created_at AS c__created_at, c.updated_at AS c__updated_at, c.latitude AS c__latitude, c.longitude AS c__longitude FROM csw__article c WHERE (c.id = '20') LIMIT 1
0.00s, "doctrine" connection
#

SELECT c.id AS c__id, c.userid AS c__userid, c.title AS c__title, c.city AS c__city, c.description AS c__description, c.image AS c__image, c.price AS c__price, c.featured AS c__featured, c.state AS c__state, c.zipcode AS c__zipcode, c.is_public AS c__is_public, c.is_activated AS c__is_activated, c.expires_at AS c__expires_at, c.created_at AS c__created_at, c.updated_at AS c__updated_at, c.latitude AS c__latitude, c.longitude AS c__longitude FROM csw__article c WHERE (c.id = '14') LIMIT 1
0.00s, "doctrine" connection
#

SELECT c.id AS c__id, c.userid AS c__userid, c.title AS c__title, c.city AS c__city, c.description AS c__description, c.image AS c__image, c.price AS c__price, c.featured AS c__featured, c.state AS c__state, c.zipcode AS c__zipcode, c.is_public AS c__is_public, c.is_activated AS c__is_activated, c.expires_at AS c__expires_at, c.created_at AS c__created_at, c.updated_at AS c__updated_at, c.latitude AS c__latitude, c.longitude AS c__longitude FROM csw__article c WHERE (c.id = '13') LIMIT 1
0.00s, "doctrine" connection
#

SELECT c.id AS c__id, c.userid AS c__userid, c.title AS c__title, c.city AS c__city, c.description AS c__description, c.image AS c__image, c.price AS c__price, c.featured AS c__featured, c.state AS c__state, c.zipcode AS c__zipcode, c.is_public AS c__is_public, c.is_activated AS c__is_activated, c.expires_at AS c__expires_at, c.created_at AS c__created_at, c.updated_at AS c__updated_at, c.latitude AS c__latitude, c.longitude AS c__longitude FROM csw__article c WHERE (c.id = '12') LIMIT 1
0.00s, "doctrine" connection
#

SELECT c.id AS c__id, c.userid AS c__userid, c.title AS c__title, c.city AS c__city, c.description AS c__description, c.image AS c__image, c.price AS c__price, c.featured AS c__featured, c.state AS c__state, c.zipcode AS c__zipcode, c.is_public AS c__is_public, c.is_activated AS c__is_activated, c.expires_at AS c__expires_at, c.created_at AS c__created_at, c.updated_at AS c__updated_at, c.latitude AS c__latitude, c.longitude AS c__longitude FROM csw__article c WHERE (c.id = '11') LIMIT 1
0.00s, "doctrine" connection
#

SELECT c.id AS c__id, c.userid AS c__userid, c.title AS c__title, c.city AS c__city, c.description AS c__description, c.image AS c__image, c.price AS c__price, c.featured AS c__featured, c.state AS c__state, c.zipcode AS c__zipcode, c.is_public AS c__is_public, c.is_activated AS c__is_activated, c.expires_at AS c__expires_at, c.created_at AS c__created_at, c.updated_at AS c__updated_at, c.latitude AS c__latitude, c.longitude AS c__longitude FROM csw__article c WHERE (c.id = '4') LIMIT 1
0.00s, "doctrine" connection
#

SELECT c.id AS c__id, c.userid AS c__userid, c.title AS c__title, c.city AS c__city, c.description AS c__description, c.image AS c__image, c.price AS c__price, c.featured AS c__featured, c.state AS c__state, c.zipcode AS c__zipcode, c.is_public AS c__is_public, c.is_activated AS c__is_activated, c.expires_at AS c__expires_at, c.created_at AS c__created_at, c.updated_at AS c__updated_at, c.latitude AS c__latitude, c.longitude AS c__longitude FROM csw__article c WHERE (c.id = '3') LIMIT 1
0.00s, "doctrine" connection
#

SELECT c.id AS c__id, c.userid AS c__userid, c.title AS c__title, c.city AS c__city, c.description AS c__description, c.image AS c__image, c.price AS c__price, c.featured AS c__featured, c.state AS c__state, c.zipcode AS c__zipcode, c.is_public AS c__is_public, c.is_activated AS c__is_activated, c.expires_at AS c__expires_at, c.created_at AS c__created_at, c.updated_at AS c__updated_at, c.latitude AS c__latitude, c.longitude AS c__longitude FROM csw__article c WHERE (c.id = '2') LIMIT 1
0.00s, "doctrine" connection
#

SELECT c.id AS c__id, c.userid AS c__userid, c.title AS c__title, c.city AS c__city, c.description AS c__description, c.image AS c__image, c.price AS c__price, c.featured AS c__featured, c.state AS c__state, c.zipcode AS c__zipcode, c.is_public AS c__is_public, c.is_activated AS c__is_activated, c.expires_at AS c__expires_at, c.created_at AS c__created_at, c.updated_at AS c__updated_at, c.latitude AS c__latitude, c.longitude AS c__longitude FROM csw__article c WHERE (c.id = '21') LIMIT 1
0.00s, "doctrine" connection
#

SELECT c.id AS c__id, c.sluggish AS c__sluggish, c.name AS c__name, c.parent_id AS c__parent_id, c.lft AS c__lft, c.rgt AS c__rgt, c.level AS c__level FROM csw__categorie c WHERE (c.parent_id = '1') ORDER BY c.parent_id ASC, c.lft ASC
0.00s, "doctrine" connection
#

SELECT s.id AS s__id, s.first_name AS s__first_name, s.last_name AS s__last_name, s.email_address AS s__email_address, s.username AS s__username, s.algorithm AS s__algorithm, s.salt AS s__salt, s.password AS s__password, s.is_active AS s__is_active, s.is_super_admin AS s__is_super_admin, s.last_login AS s__last_login, s.created_at AS s__created_at, s.updated_at AS s__updated_at FROM sf_guard_user s WHERE (s.id = '5') LIMIT 1

解决方案
You can enable xdebug_loggin to find out where you queries originate from:
//factories.yml
    dev:
      logger:
        param:
          loggers:
            sf_web_debug:
              param:
                xdebug_logging: true
This will log the trace to each query and should give you a clue what went wrong

                        
这篇关于Symfony 1.4为1个查询提供15个DB请求的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！