CActiveDataProvider not returning joined columns

In a sample blog application, I need the user details and number of posts by him. For that I used the following criteria:

$criteria->select='t.`id`, t.`name`, count(t1.`post_id`) AS `post_count`';

$criteria->join='JOIN {{posts}} `t1` ON t.`id` = t1.`user_id`';

$criteria->order='t.`name` ASC';

I used CActiveDataProvider in User model with the above criteria. When I executed the script, I got the error: "Property "User.post_count" is not defined.".

I tried a var_dump for $dataProvider->getData() and could see only the column that exists in the user table and not the column that I added in the select clause.


private '_attributes' => 


          'id' => string '30' (length=2)

          'name' => string 'TestUser' (length=<img src='' class='bbc_emoticon' alt='8)' />

      private '_related' => 




Am I missing some basics here?

You’d better use statistical query:

Thanks andy_s, I did not know about the statistical query.

But I just want to know why the previous code is not working. Everything seems perfect in that. Can somebody tell me what is wrong in the above code?

Check the generated SQL. To do so, configure a CWebLogroute in main.php, enable YII_DEBUG and don’t use yiit.php. You’ll see the SQL on the end of your page. That should help you to find out, what’s wrong.

I did so and checked the sql query:

SELECT t.`id`, t.`name`, count(t1.`post_id`) AS `post_count` FROM

`ss_users` `t` JOIN ss_posts `t1` ON t.`id` = t1.`user_id`

ORDER BY t.`name` ASC LIMIT 10

As you can see, the SQL query has the post_count column.

To check whether there is any problem in mysql(just in case…), i executed the query in mysql and i got all the three columns.

I’m confused!! ???

To test it I tried removing the count function and renamed the name column as follows:

$criteria->select='t.`id`, t.`name` AS `user_name`';

the sql query shows the column - AS user_name. But in var_dump i could only see the result for Is it a bug? Has nobody tried this?

I think you have to define a "user_name" property in your model.

Thanks andy_s. I didn’t think of that. We have to define a property in model before using it.

Thanks again.