yii \ db \ Query :: limit（）函数不限制记录

yii \ db \ Query :: limit（）函数不限制记录 - Yii2(yii\db\Query::limit() function not limiting the records - Yii2)

下面是我用来从我的表中获取数据以创建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());

我根据Yii官方文档http://www.yiiframework.com/doc-2.0/yii-db-querytrait.html#limit（）设置了限制（1），它设置了 “ 设置查询的LIMIT部分。 ” - 详细说明 。

当我执行上述查询时，dataprovider返回所有记录。

我的代码出了什么问题？

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?

最满意答案

ActiveDataProvider不关注查询限制。

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

从以上链接中提取：

注意：如果查询已指定orderBy子句，则最终用户（通过排序配置）给出的新排序说明将附加到现有的orderBy子句。 任何现有的限制和偏移子句将被最终用户的分页请求（通过分页配置）覆盖。

因此，由于您有固定数据，请使用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]);

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.