Result from SQL query and CActiveDataProvider different


I am having some difficulties. I have to use CActiveDataProvider for retrieving data using a lot of joins (with CDbCriteria "with") and I have to show data from more models.

When I use just the SQL query, I get a good set of results. But CActiveDataProvider excludes those result with same primary key of the primary model (Article). So Article has a category, and many categories have many blogs. I have to get all blogs that are matched by article’s category. Since there are more blogs I have to get two rows with the same Article, but another Blog. Just one of them shows up.

How could I solve this?

EDIT: I found out where’s the problem. It’s not that CActiveDataProvider excludes some results, it just creates a hierarchical result set, so when there are entries with the same id, you have to access an array ($data->article[0]->nextRelation), and to get all results, you’d have to nest foreach loops, but i don’t want that, i must have all data in the top “layer”, so I can access all data in just one loop: foreach($data) $data->someRelation->someOtherRelation->someAttribute.

You can probably reverse the querys relation chain. Blog BELONGS_TO (the articles) Category BELONGS_TO Article.


I’m not sure that will solve the problem. This is my SQL result:

article_id | category_id | blog_id | blog_name       | ...


     1     |      1      |    1    | 1. blog         | ...


     1     |      1      |    2    |2. blog,same cat.| ...


     2     |      2      |    3    | 3. blog         | ...


     3     |      3      |    4    | 4. blog         | ...


So, I have to get 4 results, with CActiveDataProvider I get 3 of them. There is article 1, 2 and 3, not 1, 1, 2, 3. I can access those two blogs with something like this article[1]->blog[1] or ->blog[2]. But I have to get exactly the same result set as in the example above.

This is not a blogging system, so the relations are not as you would think they are.

If you don’t plan to use CGridView you can easily do it your way.

foreach ($dataprovider->data as $article)


  foreach ($article->categories as $category)


    foreach ($category->blogs as $blog)


BTW the MANY_MANY relationship complicates my approach. The through mechanism may help with that (totally untested on my part).

Also, you may consider using CSqlDataProvider (also untested).


Well, that’s the whole point, I want to use CGridView for sorting, pagination and ajax search. Fetching the data on my own makes that a lot more complicated.

I think this happens with all data providers, whether it’s a CActiveDataProvider, CArrayDataProvider or CSqlDataProvider. There is a method fetchKeys(), in all of those clases, that fetches keys uniquely, not allowing to have more equal id’s in the top “layer”.

There is no way around this?

The way CActiveDataProvider fetches data is so annoying and illogical. If the query is a little more complicated, it adds non-existing data and shows some results which don’t even exist when you run the sql query in phpmyadmin or something like that. So I have to use plain sql queries, which pretty much eliminates all the positive things of active record and Yii in general. I’m really disappointed with this.

If blogs is the primary table data then use the Blog model as the CActiveDataProvider class?

article_id | category_id | blog_id

new CActiveDataProvider( 'Blog', array(

    'criteria' => array(

        'with' => array( 'categories', 'categories.articles' ),

        'together' => true


) );

Would this turning on it’s head make it work for you. Failing that you could consider a database view and base an Active Record on that.