有一个选择，在学说中执行 10 个查询(Symfony)

2022-01-16 00:00:00 mysql doctrine symfony1

我有一个执行 11 个查询的大查询，我不知道问题出在哪里，但发现问题出在 select a did for geo 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;
}

推荐答案

我会将所有 where 更改为 whereIn，例如:

I would change all where by whereIn like:

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

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

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

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.

相关文章