doudiecai1572 2014-07-24 13:57
浏览 34
已采纳

为什么Yii QueryBuilder会忽略offset()?

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?

  • 写回答

2条回答 默认 最新

  • dongliao9233 2014-07-25 11:37
    关注

    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();
    
    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论
查看更多回答(1条)

报告相同问题?

悬赏问题

  • ¥15 用前端向数据库插入数据,通过debug发现数据能走到后端,但是放行之后就会提示错误
  • ¥15 python天天向上类似问题,但没有清零
  • ¥30 3天&7天&&15天&销量如何统计同一行
  • ¥30 帮我写一段可以读取LD2450数据并计算距离的Arduino代码
  • ¥15 C#调用python代码(python带有库)
  • ¥15 矩阵加法的规则是两个矩阵中对应位置的数的绝对值进行加和
  • ¥15 活动选择题。最多可以参加几个项目?
  • ¥15 飞机曲面部件如机翼,壁板等具体的孔位模型
  • ¥15 vs2019中数据导出问题
  • ¥20 云服务Linux系统TCP-MSS值修改?