为什么Yii QueryBuilder会忽略offset（）？

Question

My query result with offset = 0 and limit = 10 is exactly one row.

When I use offset = 1 there should be no result.

But with a query like

$query = Yii::app()->db->createCommand();
$criteria = new CDbCriteria();
$criteria->addCondition('f.deleted = 0 AND b.version = f.version');
$criteria->params = [];

if ($idUserAnswerer)
{
    $query->leftJoin('bar AS b', 'b.idF = f.id');
    $criteria->addCondition('f.idUserOwner = :idUserOwner');
    $criteria->params['idUserOwner'] = $idUserOwner;
}

$query->select('*')
    ->from('foo AS f')
    ->where($criteria->condition)
    ->bindValues($criteria->params);

$query->order('FIELD(t.type,
    ' . X::X_TYPE_ID_A . ', ' . X::X_TYPE_ID_B . ',
    ' . X::X_TYPE_ID_C . '), b.created_time, b.votes DESC');

$data = $query->limit(10, 2)->queryAll();

(Hint: I have to use $criteria, because I have to use inCondition at some point)

I still get this one row. But normally (when I do this query directly in my db) I should not get a result.

Any ideas why Yii spits out this one row?

Answer 1

This is a problem with the query builder. You need a specific order, binding values should be done at the end of the query.

This is because Yii does a prepare statement when binding values. It then caches this statement. Then you append your order and limit to it. When you do the query, the previous cached statement is used, without the order and limit. I think this is by design.

So always bind the values last:

$query = Yii::app()->db->createCommand();
$data = $query->select('*')
    ->from('tbl AS t')
    ->where('idCategory=:catid')
    ->order('id DESC')
    ->limit(2, 2)
    ->bindValues(array(':catid' => 151))
    ->queryAll();