CActiveDataProvider not returning joined columns

Yii 1.1 General Discussion
1

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

        array

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

          'name' => string 'TestUser' (length=<img src='http://www.yiiframework.com/forum/public/style_emoticons/default/cool.gif' class='bbc_emoticon' alt='8)' />

      private '_related' => 

        array

          empty

...

Am I missing some basics here?

2

You’d better use statistical query: http://www.yiiframework.com/doc/guide/database.arr#statistical-query

3

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?

4

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.

5

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!! ???

6

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 - t.name AS user_name. But in var_dump i could only see the result for t.id. Is it a bug? Has nobody tried this?

7

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

8

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

Thanks again.