Result from SQL query and CActiveDataProvider different

Yii 1.1 General Discussion
1

Hello,

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.

2

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

/Tommy

3

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.

4

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

/Tommy

5

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?

6

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.

7

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.