我正在尝试获取CActiveDataProvider样式的数据,以便将数据传递到相应视图中的CGridView .
我尝试以这种方式使用CActiveData Provider获取关系数据:
- 我有三张表如下:
CREATE TABLE tbl_test_location
( locationId
int(11)NOT NULL AUTO_INCREMENT, locationName
varchar(255)DEFAULT NULL,PRIMARY KEY( locationId
))ENGINE = InnoDB AUTO_INCREMENT = 6 DEFAULT CHARSET = utf8;
CREATE TABLE tbl_test_user
( userId
int(11)NOT NULL AUTO_INCREMENT, userName
varchar(255)DEFAULT NULL,PRIMARY KEY( userId
))ENGINE = InnoDB AUTO_INCREMENT = 3 DEFAULT CHARSET = utf8;
CREATE TABLE tbl_test_location_user_assignment
( locationId
int(11)NOT NULL, userId
int(11)NOT NULL,PRIMARY KEY( locationId
, userId
),KEY fk_tlua_user
( userId
),CONSTRAINT fk_tlua_location
FOREIGN KEY( locationId
)REFERENCES tbl_test_location
( locationId
)ON DELETE CASCADE, CONSTRAINT fk_tlua_user
FOREIGN KEY( userId
)REFERENCES tbl_test_user
( userId
)ON DELETE CASCADE)ENGINE = InnoDB DEFAULT CHARSET = utf8;
- 模型中的关系是:
/models/TestLocation.php
'tblTestUsers' => array(self::MANY_MANY, 'TestUser', '{{test_location_user_assignment}}(locationId, userId)'),
/models/TestUser.php
'tblTestLocations' => array(self::MANY_MANY, 'TestLocation', '{{test_location_user_assignment}}(userId, locationId)'),
- 控制器中名为actionIndexOwn的方法是:注意:用户可以有多个位置,一个位置可以有多个用户 .
/controllers/TestLocationController.php
public function actionIndexOwn() { $dataProvider=new CActiveDataProvider('TestLocation', array( 'criteria'=>array( 'with'=>array( 'tblTestUsers'=>array( 'condition'=>'tbl_test_user.userId=1', ), ), ), )); $this->render('index',array( 'dataProvider'=>$dataProvider, )); }
- ,视图是:
视图/ testLocation / index.php的
<?php $this->widget('zii.widgets.CListView', array( 'dataProvider'=>$dataProvider, 'itemView'=>'_view', )); ?>
我收到了这个错误:
CDbCommand failed to execute the SQL statement: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'tbl_test_user.userId' in 'where clause'. The SQL statement executed was: SELECT COUNT(DISTINCT `t`.`locationId`) FROM `tbl_test_location` `t` LEFT OUTER JOIN `tbl_test_location_user_assignment` `tblTestUsers_tblTestUsers` ON (`t`.`locationId`=`tblTestUsers_tblTestUsers`.`locationId`) LEFT OUTER JOIN `tbl_test_user` `tblTestUsers` ON (`tblTestUsers`.`userId`=`tblTestUsers_tblTestUsers`.`userId`) WHERE (tbl_test_user.userId=1)
应用程序日志:
CDbCommand::fetchColumn() failed: SQLSTATE[42S22]: Column not found: 1054
Unknown column 'tbl_test_user.userId' in 'where clause'. The SQL statement
executed was: SELECT COUNT(DISTINCT `t`.`locationId`) FROM
`tbl_test_location` `t` LEFT OUTER JOIN
`tbl_test_location_user_assignment` `tblTestUsers_tblTestUsers` ON
(`t`.`locationId`=`tblTestUsers_tblTestUsers`.`locationId`) LEFT OUTER JOIN
`tbl_test_user` `tblTestUsers` ON
(`tblTestUsers`.`userId`=`tblTestUsers_tblTestUsers`.`userId`) WHERE
(tbl_test_user.userId=1).
in C:\htdocs\RackDomain\protected\views\testLocation\index.php (20)
in C:\htdocs\RackDomain\protected\controllers\TestLocationController.php
(147)
有人可以帮助我,我尝试了很多方法来编写关系CActiveDataProvider,但我总是得到相同的错误...
非常感谢你的帮助!
2 回答
正如您可以看到的那样,Yii在其sql查询中使用别名来表名 . 所以你也应该使用这个别名 . 更多的是在 ```` 中放置表名,列名和别名更不容易出错 .
您的代码应如下所示:
如果要使用关系数据字段,只需将关系名称与字段名称一起使用即可 .
这里 "tblTestUsers" 是模型类中定义的关系名称 .