donglinxin8765
2016-04-05 12:11 阅读 355
已采纳

yii \ db \ Query :: limit()函数不限制记录 - Yii2

Below is the code I'm using to get data from my table(s) for creating REST api.

$query = new yii\db\Query();
$sql = $query
    ->select('a.vehicle_number, b.device_id, b.dated, b.speed, b.ignition, b.latitude, b.longitude')
    ->from('tk103_devices a, tk103_current_location b')
    ->where('a.device_id = b.device_id AND a.transporter_id='.$id)
    ->orderBy(['a.vehicle_number'=>SORT_ASC])
    ->limit(1);

$dataProvider = new ActiveDataProvider([
    'query'=>$sql
    ]);
return array('count_flag'=>$countFlag, 'dataProvider'=>$dataProvider->getModels());

I had set the limit(1) which does "Sets the LIMIT part of the query." as per Yii official doc http://www.yiiframework.com/doc-2.0/yii-db-querytrait.html#limit()-detail.

When I am executing the above query, all the records are being returned by the dataprovider.

What's wrong with my code?

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享

2条回答 默认 最新

  • 已采纳
    duaabhuv188411 duaabhuv188411 2016-04-05 13:39

    Did some homework by myself, I find solution to the above problem by changing the code as below:

    $query = new yii\db\Query();
    $sql = $query
        ->select('a.vehicle_number, b.device_id, b.dated, b.speed, b.ignition, b.latitude, b.longitude')
        ->from('tk103_devices a, tk103_current_location b')
        ->where('a.device_id = b.device_id AND a.transporter_id='.$id)
        ->orderBy(['a.vehicle_number'=>SORT_ASC])
        ->one();
    
    $dataProvider = new ActiveDataProvider([
        'query'=>$sql
        ]);
    return array('count_flag'=>$countFlag, 'dataProvider'=>$dataProvider);
    

    As per my scenario, I wanted to retrieve only first record. So, I used one() instead of limit(1).

    Secondly, I was returning dataProvider as $dataProvider->getModels(). I changed this to $dataProvider only. Since "ActiveDataProvider does not take care at query limit." as per Fabrizio Caldarelli's answer below (or) above, it was returning all retrieved records.

    Hope that helps someone having related issues.


    For previous code to work, you must see Fabrizio Caldarelli's answer below (or) above.

    点赞 评论 复制链接分享
  • doulu4233 doulu4233 2016-04-05 13:14

    ActiveDataProvider does not take care at query limit.

    http://www.yiiframework.com/doc-2.0/guide-output-data-providers.html#active-data-provider

    Extract from above link:

    Note: If a query already specifies the orderBy clause, the new ordering instructions given by end users (through the sort configuration) will be appended to the existing orderBy clause. Any existing limit and offset clauses will be overwritten by the pagination request from end users (through the pagination configuration).

    So, since you have fixed data, use ArrayDataProvider:

    $data = $query
        ->select('a.vehicle_number, b.device_id, b.dated, b.speed, b.ignition, b.latitude, b.longitude')
        ->from('tk103_devices a, tk103_current_location b')
        ->where('a.device_id = b.device_id AND a.transporter_id='.$id)
        ->orderBy(['a.vehicle_number'=>SORT_ASC])
        ->limit(1)
        ->all();
    
    $dataProvider = new \yii\data\ArrayDataProvider(['allModels' => $data]);
    
    点赞 评论 复制链接分享

相关推荐