关闭

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

标签: 1052yii 1052yii id排序报错Integrity constraint
3349人阅读 评论(0) 收藏 举报
分类:

在使用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',
)

0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:919392次
    • 积分:6989
    • 等级:
    • 排名:第3461名
    • 原创:100篇
    • 转载:3篇
    • 译文:2篇
    • 评论:39条
    最新评论