得到一个选择，在教义（Symfony）10查询 [英] got a select that does 10 query in doctrine (Symfony)

问题描述

我有一个大查询执行11查询，我不知道问题在哪里，但发现问题是在一个选择一个地理loc，任何人有一个想法如何纠正？

I got a big query that execute 11 query, I didnt know where the problem is, but found the problem was in a select a did for geo loc, anyone has an idea how to correct that?

只有当我使用这个查询SfDoctrinePaginate

It happens only when i use this query SfDoctrinePaginate

进一步调查$ q-> select（a.longitude）创建尽可能多查询..

Investigating further $q->select("a.longitude") create as much queries..

问题：

$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");

完整的型号：

public function getListItems($orderby, $budget, $motscles, $userid, $catID, $useDistance = false)
{
     $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->WhereIn('a.userid = ?', $userid);
     }


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

     }        
     $q->execute();      
     return $q;
}

推荐答案

我会更改所有其中由 whereIn 如：

if($userid){
    $q->andWhereIn('a.userid', $userid);
}

if($catID){
    $q->andWhereIn('ca.categorie_id', $catID);
}

我认为这是因为当您在视图中使用结果时paginator无法连续获取所有记录，因此每个项目都必须执行查询以获取所有字段。

I think this happens because when you're using the results in the view the paginator cant fetch all records in a row, so for each item has to do the query to get all fields.

这篇关于得到一个选择，在教义（Symfony）10查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！