Yii报错：SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'id' in order clause is ambiguous

在使用Yii框架gridview通过id排序时，会报这个错误：

CDbCommand 无法执行 SQL 语句: SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'id' in order clause is ambiguous. 

这个问题在你当前表模型中，关联了其他表的时候，会报这个错误。意思是“id字段是模棱两可的，模糊的定义”，这是因为在你的关联表中也有一个id字段，所以程序分不清楚你引用的到底是哪一个表的id字段。

比如对我的FarmProduce模型类中进行id降序排列：

<span style="font-family:FangSong_GB2312;font-size:12px;">return new CActiveDataProvider($this, array('criteria'=>$criteria,'sort'=>array('defaultOrder'=>'id DESC',)));</span>

FarmProduce模型类中定义了如下关系：

<span style="font-family:FangSong_GB2312;font-size:12px;">public function relations(){// NOTE: you may need to adjust the relation name and the related// class name for the relations automatically generated below.return array('units' => array(self::BELONGS_TO, 'Units', 'unitid'),//, 'with'=>'unit_en''provinces' => array(self::BELONGS_TO, 'Provinces', 'pid'),'cities' => array(self::BELONGS_TO, 'Cities', 'cid'),'areas' => array(self::BELONGS_TO, 'Areas', 'aid'),);}</span>

关联的units、procinces、cities和areas四张表中有一个或多个也有id字段，这样的话，在排序的时候

'sort'=>array(
'defaultOrder'=>'id DESC',
)

指定了id为排序字段，yii就不知道你到底使用当前表的id还是关联表中的id作为排序的id，才会报错。

FarmProduce模型类对应的表名是：farm_produce，如果在id前加上当前表名前缀进行说明行不行呢？如下：

'sort'=>array(
'defaultOrder'=>'farm_produce.id DESC',
)

很遗憾是不行的，会报错：

CDbCommand 无法执行 SQL 语句: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'farm_produce.id' in 'order clause'.The SQL statement executed was: SELECT `t`.`id` AS `t0_c0`, `t`.`publish_no` AS `t0_c1`, `t`.`name` AS `t0_c2`, `t`.`unitid` AS `t0_c3`, `t`.`pid` AS `t0_c4`, `t`.`cid` AS `t0_c5`, `t`.`aid` AS `t0_c6`, `t`.`pic` AS `t0_c7`, `units`.`id` AS `t1_c0`, `units`.`unit_en` AS `t1_c1`, `units`.`unit_zh` AS `t1_c2`, `units`.`note` AS `t1_c3`, `provinces`.`id` AS `t2_c0`, `provinces`.`provinceid` AS `t2_c1`, `provinces`.`province` AS `t2_c2`, `cities`.`id` AS `t3_c0`, `cities`.`cityid` AS `t3_c1`, `cities`.`city` AS `t3_c2`, `cities`.`provinceid` AS `t3_c3`, `areas`.`id` AS `t4_c0`, `areas`.`areaid` AS `t4_c1`, `areas`.`area` AS `t4_c2`, `areas`.`cityid` AS `t4_c3` FROM `farm_produce` `t` LEFT OUTER JOIN `units` `units` ON (`t`.`unitid`=`units`.`id`) LEFT OUTER JOIN `provinces` `provinces` ON (`t`.`pid`=`provinces`.`id`) LEFT OUTER JOIN `cities` `cities` ON (`t`.`cid`=`cities`.`id`) LEFT OUTER JOIN `areas` `areas` ON (`t`.`aid`=`areas`.`id`) ORDER BY farm_produce.id DESC LIMIT 10

意思是无法识别'farm_produce.id'字段，汗……

因为yii会把farm_produce.id整体看做一个字段名，当然在数据库中没有了。不过报错的代码中我们可以看到【FROM `farm_produce` `t`】，我的表名是farm_produce，yii在生成SQL语句的时候，对当前表生成一个别名't'，我们只要在当前表模型类中，使用't'作为当前表就可以了，所以程序应该这么改：

'sort'=>array(
'defaultOrder'=>'t.id DESC',
)