Yii cdbcriteria select a relation's columns

Question

I am having very difficult time to select usernames of all posts in the blog demo given in Yii..

author is relation of post class with user...

$criteria = new CDbCriteria;
$criteria->with='author';
$criteria->select='author.username';
$dataProvider=new CActiveDataProvider('Post', array(
    'criteria' => $criteria,
));
var_dump($dataProvider->getData());

Error:

Active record "Post" is trying to select an invalid column "author.username". Note, the column must exist in the table or be an expression with alias.

Answer 1

what with does is eager loading..that means the relation's data will also loaded from database alongwith, and when when u'll call the relation, there won't be a actual query..

What select does is it selects it from database and maps it to model variable..

Now in your case what is happening is you're trying to write some relation's column in select, which will be there in select even without writing it, but as there is no corresponding variable to map this value yii is throwing an error..

So first thing if you need to username of auther in response, you can get it by relation calling, which won't be a database call, and u dont need to write a select..

And if u want to call the username as a part of the post model only u have got to declare it as a property in model, and then specify alias in select..

$criteria = new CDbCriteria;
$criteria->with='author';
$criteria->select='author.username as auther_username';
$dataProvider=new CActiveDataProvider('Post', array(
    'criteria' => $criteria,
));
var_dump($dataProvider->getData());

and in your Post model declare..

public $auther_username;

Now it won't throw error, and you can access the username by both ways..$post->auther_username, and $post->auther->username

Answer 2

Try this:

$criteria = new CDbCriteria;
$criteria->with=array('author'=>array('select'=>'username'));

// you can still select Post table columns here
$criteria->select='post_content';

$dataProvider=new CActiveDataProvider('Post', array(
   'criteria' => $criteria,
));
var_dump($dataProvider->getData());

Answer 3

Try this:

$criteria = new CDbCriteria;
$criteria->with=array('author'=>array('select'=>'username'));
$dataProvider=new CActiveDataProvider('Post', array(
    'criteria' => $criteria,
));
var_dump($dataProvider->getData());

Source: CActiveRecord.with

In order to customize the options on the fly, we should pass an array parameter to the with() method. The array keys are relation names, and the array values are the corresponding query options.