select distinct and CDbCriteria

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

		$criteria->select = array(







			'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('', $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);


			"(t.status_id != " . StatusBehavior::getID("private") .

			" OR t.user_id = " . Yii::app()->user->getID() . ")");

		return new CActiveDataProvider(get_class($this), array(

			'criteria' => $criteria,




			'sort' => array(

				'defaultOrder' => '',

				'attributes' => array(

					'user' => array(


						'desc'=>'username DESC',


					'' => array(


						'desc'=>'domain DESC',


					'name' => array(


						'desc'=>' DESC',


					'date_created' => array(


						'desc'=>'t.date_created DESC',


					'status_id' => array(


						'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.

Try this way of defining criteria for related tables


$criteria->with = array(

  'user' => array(





  ' => array(







(not tested)


Thanks for your help Tommy.

It is no longer erroring.

Only now I realise that the main primary key is unique so causing to many results. Doh :confused:

I need for the update.

Have think on that one.