Yii: Relational CActiveDataProvider does not working, got error "Column not found: 1054 Unknown column"

Question

I'm trying to get data in CActiveDataProvider style in order to pass data to the CGridView in the respective view.

I try to get relational data using CActiveData Provider in this way:

1. I have three tables as follows:

   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;

2. The relations in the models, are:

/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)'),

3. The method in the controller called actionIndexOwn, is: Note: The user can have various locations and one location can have many users.

/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, )); }

4. and the view is:

views/testLocation/index.php

<?php $this->widget('zii.widgets.CListView', array( 'dataProvider'=>$dataProvider, 'itemView'=>'_view', )); ?>

I got this error:

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)

The application log:

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)

Can somebody help me, I try many ways to write the relational CActiveDataProvider but I always get the same error...

Thanks a lot for your help!

Answer 1

As you can can see in error, Yii uses aliases to tables name in it's sql queries. So you should use this alias too. Whats more putting table names, column names and aliases in `` is less error prone.

Your code should look like this:

public function actionIndexOwn()
{
    $dataProvider=new CActiveDataProvider('TestLocation', array(
        'criteria'=>array(
            'with'=>array(
                'tblTestUsers'=>array(
                    'condition'=>'`tblTestUsers`.`userId`=1',
                ),
            ),
        ),
    ));
    $this->render('index',array(
        'dataProvider'=>$dataProvider,
    ));
}

Answer 2

If You want to use relational data field , just use relation name with field name.

$dataProvider=new CActiveDataProvider('TblTestLocation', array(
            'criteria'=>array(
                'with'=>array(
                    'tblTestUsers'=>array(
                        'condition'=>'tblTestUsers.userId=1',
                    ),
                ),
            ),
        ));

Here "tblTestUsers" is the name of relation defined in the model class.