I am trying to create a CActiveDataProvider for a CGridView, the results need to be both DISTINCT and limited to certain SELECT values in order for the correct results to be returned.
I have two problems.
Firstly, regardless of what I include in the CDbCriteria select parameter, it always returns all the columns. The select is just ignored.
Secondly, select is giving me an error on columns from joined tables.
public function listForGroup()
{
$criteria=new CDbCriteria;
$criteria->distinct = true;
$criteria->with = array('user', 'user.site');
$criteria->select = array(
't.offer_type_id',
't.date_created',
't.name',
't.cat_id',
't.user_id',
't.status_id',
'user.user_id', // This one does work!
'user.username', // Does not work
'user.site_id', // Does not work
'site.site_id', // Does not work
'site.domain', // Does not work
);
$criteria->compare('t.date_created', $this->date_created, true);
$criteria->compare('t.name', $this->name, true);
$criteria->compare('site.domain', $this->domain, true);
$criteria->compare('user.username', $this->username, true);
$criteria->compare('t.status_id', $this->status_id);
$criteria->addCondition(
"(t.status_id != " . StatusBehavior::getID("private") .
" OR t.user_id = " . Yii::app()->user->getID() . ")");
return new CActiveDataProvider(get_class($this), array(
'criteria' => $criteria,
'pagination'=>array(
'pageSize'=>5,
),
'sort' => array(
'defaultOrder' => 't.name',
'attributes' => array(
'user' => array(
'asc'=>'username',
'desc'=>'username DESC',
),
'user.site' => array(
'asc'=>'domain',
'desc'=>'domain DESC',
),
'name' => array(
'asc'=>'t.name',
'desc'=>'t.name DESC',
),
'date_created' => array(
'asc'=>'t.date_created',
'desc'=>'t.date_created DESC',
),
'status_id' => array(
'asc'=>'status_id',
'desc'=>'status_id DESC',
),
),
),
));
}
All the compares work, showing that the relations are fine. But here they are anyway:
'user' => array(self::BELONGS_TO, 'User', 'user_id', 'joinType'=>'INNER JOIN'),
and in the user model
'site' => array(self::BELONGS_TO, 'Site', 'site_id', 'joinType'=>'INNER JOIN'),
The error:
Active record "OfferType" is trying to select an invalid column "user.username". Note, the column must exist in the table or be an expression with alias.
I’ve tried using both a string and an array for the select.
Any help with either problem would be much appreciated.