CGridView with relations, ID filter doesn't work `Integrity constraint violation`

Question

I'm trying to make a cgridview that can filter data from relations table. This is my first time with CGridView and I just can't get it to work. Help is appreciated. I have 2 problems. first: i have managed to get a dropdown filter of my users (avtor, relation in Novica), but when I choose one, it always returns this: "No results found.". second: When I try to filter with Novica.ID, i get this error: (filtering with other columsn works great)

Error 500:

CDbException

CDbCommand failed to execute the SQL statement: SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'id' in where clause is ambiguous. The SQL statement executed was: SELECT COUNT(*) FROM novica t LEFT OUTER JOIN uporabnik avtor ON (t.uporabnik_id=avtor.id) WHERE ((uporabnik_id = :id) AND (id=:ycp0)) (C:\xampp\htdocs\PEF\framework\db\CDbCommand.php:528)

0
C:\xampp\htdocs\PEF\framework\db\CDbCommand.php(425):
CDbCommand->queryInternal('fetchColumn', 0, Array) 1
C:\xampp\htdocs\PEF\framework\db\ar\CActiveFinder.php(762):
CDbCommand->queryScalar() 2
C:\xampp\htdocs\PEF\framework\db\ar\CActiveFinder.php(155):
CJoinElement->count(Object(CDbCriteria)) 3
C:\xampp\htdocs\PEF\framework\db\ar\CActiveRecord.php(1540):
CActiveFinder->count(Object(CDbCriteria)) 4
C:\xampp\htdocs\PEF\framework\web\CActiveDataProvider.php(179):
CActiveRecord->count(Object(CDbCriteria)) 5
C:\xampp\htdocs\PEF\framework\web\CDataProvider.php(193):
CActiveDataProvider->calculateTotalItemCount() 6
C:\xampp\htdocs\PEF\framework\web\CActiveDataProvider.php(129):
CDataProvider->getTotalItemCount() 7
C:\xampp\htdocs\PEF\framework\web\CDataProvider.php(137):
CActiveDataProvider->fetchData() 8
C:\xampp\htdocs\PEF\framework\zii\widgets\CBaseListView.php(105):
CDataProvider->getData() 9
C:\xampp\htdocs\PEF\framework\zii\widgets\grid\CGridView.php(269):
CBaseListView->init() 10
C:\xampp\htdocs\PEF\framework\web\CBaseController.php(148):
CGridView->init() 11
C:\xampp\htdocs\PEF\framework\web\CBaseController.php(173):
CBaseController->createWidget('zii.widgets.gri...', Array) 12
C:\xampp\htdocs\PEF\protected\views\adminNovica\index.php(28):
CBaseController->widget('zii.widgets.gri...', Array) 13
C:\xampp\htdocs\PEF\framework\web\CBaseController.php(127):
require('C:\xampp\htdocs...') 14
C:\xampp\htdocs\PEF\framework\web\CBaseController.php(96):
CBaseController->renderInternal('C:\xampp\htdocs...', Array, true) 15
C:\xampp\htdocs\PEF\framework\web\CController.php(870):
CBaseController->renderFile('C:\xampp\htdocs...', Array, true) 16
C:\xampp\htdocs\PEF\protected\controllers\AdminNovicaController.php(19):
CController->renderPartial('index', Array) 17
C:\xampp\htdocs\PEF\framework\web\actions\CInlineAction.php(50):
AdminNovicaController->actionIndex() 18
C:\xampp\htdocs\PEF\framework\web\CController.php(309):
CInlineAction->runWithParams(Array) 19
C:\xampp\htdocs\PEF\framework\web\CController.php(287):
CController->runAction(Object(CInlineAction)) 20
C:\xampp\htdocs\PEF\framework\web\CController.php(266):
CController->runActionWithFilters(Object(CInlineAction), Array) 21
C:\xampp\htdocs\PEF\framework\web\CWebApplication.php(276):
CController->run('') 22
C:\xampp\htdocs\PEF\framework\web\CWebApplication.php(135):
CWebApplication->runController('adminnovica') 23
C:\xampp\htdocs\PEF\framework\base\CApplication.php(162):
CWebApplication->processRequest() 24
C:\xampp\htdocs\PEF\index.php(13): CApplication->run() 25 {main}

These are my tables:

Models: Novica (eng: News)

public function relations() {
        return array(
            'uporabnik' => array(self::BELONGS_TO, 'DostopNovica', 'uporabnik_id'),
            'avtor' => array(self::BELONGS_TO, 'Uporabnik', 'uporabnik_id'),
        );
    }

public function search() {
        $criteria = new CDbCriteria;
        $criteria->compare('id', $this->id);
        $criteria->with = 'avtor';
        $criteria->compare('naslov', $this->naslov, true);
        $criteria->compare('datum', $this->datum, true);
        $criteria->compare('avtor.upime', $this->uporabnik_id, true); //avtor.upime (eng. author.username)

    return new CActiveDataProvider(get_class($this), array(
                'criteria' => $criteria,
                'sort' => array(
                    'defaultOrder' => 'datum DESC', //date
                ),
                'pagination' => array(
                    'pageSize' => 5
                ),
            ));
}

Action:

 public function actionIndex() {
            $model = new Novica('search');
            if (isset($_GET['Novica']))
                $model->attributes = $_GET['Novica'];

            $params = array(
                'model' => $model,
            );

            if (!isset($_GET['ajax']))
                $this->render('index', $params);
            else
                $this->renderPartial('index', $params);
        }

CGridView:

$this->widget('zii.widgets.grid.CGridView', array(
    'dataProvider' => $model->search(),
    'filter' => $model,
    'columns' => array('id',
        array('name' => 'datum',
            'value' => 'date("j.n.Y G:i", strtotime($data->datum))'
        ),
        array(
            'name' => 'uporabnik_id',
            'value' => 'Uporabnik::Model()->FindByPk($data->uporabnik_id)->upime',
            'filter' => CHtml::listData(Uporabnik::model()->findAll(), 'id', 'upime'),
        ),
        'naslov'
    //specify the colums you wanted here
    ),
));

Rajat Singhal Rajat Singhal · Accepted Answer · 2012-06-20T18:14:54

Problem 1:

In your model use

$criteria->compare('avtor.id', $this->uporabnik_id, true);

compare with id of relation avtor because that is what you have defined as value in dropdown in your gridview..CHtml::listData(Uporabnik::model()->findAll(), 'id', 'upime')..listData syntex is models, value, text..

Problem 2:

In your model use

$criteria->compare('t.id', $this->id);

t is the default alias used by the yii for the table...the problem with ambigious is that column id exists in both tables..

CGridView with relations, ID filter doesn't work `Integrity constraint violation`

CDbException

2 Answers