php - (Yii)CDbCommand执行SQL语句失败: SQLSTATE[42000]: Syntax error or access violation:

Question

我正在尝试执行此 sql 查询，但发生异常...我在 PhpMyAdmin 中尝试了该查询，并且运行正常。

<?php
        $criteria=new CDbCriteria;
        $criteria->condition='select min(purchase_order_id) from purchase_order where purchase_order_id >'.$model->purchase_order_id;
        $nextfield = \POrder::model()->model()->findAll($criteria);
           ?>

异常(exception)的是，CDbCommand 无法执行 SQL 语句: SQLSTATE[42000]:语法错误或访问冲突:1064 您的 SQL 语法有错误；检查与您的 MySQL 服务器版本相对应的手册，了解在第 1 行的“select min(purchase_order_id) from buy_order where buy_order_id >65”附近使用的正确语法。执行的 SQL 语句是:SELECT * FROM purchase_order t WHERE select min(purchase_order_id) from buy_order where buy_order_id >65

Answer 1

如果您只想要查询结果，请执行以下操作:

Yii::app()->db->createCommand("
SELECT MIN(purchase_order_id) FROM purchase_order
WHERE purchase_order_id > :purchase_order_id
")->queryScalar(array('purchase_order_id'=>$model->purchase_order_id'));

这将返回您从 phpmyadmin 或 mysql 工作台获得的完全相同的单个值。

如果您想要与该purchase_order_id对应的型号记录，那么您可以执行以下操作:

$criteria=new CDbCriteria;
$criteria->addCondition("t.purchase_order_id = (
SELECT MIN(purchase_order_id)
FROM purchase_order
WHERE purchase_order_id > :previous_purchase_order_id)");
$criteria->params['previous_purchase_order_id'] = $model->purchase_order_id;
$nextfield = \POrder::model()->model()->find($criteria);

我假设只有一个这样的模型，因此我将 findAll 更改为 find。