CListView with Joined tables and a pager - do-able?

Hi all,

This is the use case:

Two related tables, with HAS_MANY relationship:

I need to show a CListView from the joined tables, where the condition depends on content of both tables. I also need to have a pager.

Current findings:

So I have it working - the list view is showing correct data and I have the pager. The problem is that the pager is weird. If I have 15 entries and I requested the pageSize to be 10, its giving me ‘1-8 of 15’ results on the first page. If I lower the pageSize to 5 the resulted table is 3 items on the first page, 5 on the second, and it so on - inconsistent paging. The displayed results themselves look ok.

Searching for an explanation, I stumbled upon this old forum post which basically says that its impossible to have a pager on a joined table (HAS_MANY relation) with the ‘together’ option. I have to use the latter in order to avoid other fatal problem that will occur if related tables are not joined in advance, which is what the ‘together’ does.

Is there any way I can have a CListView which is a result of a joined tables (HAS_MANY) relationship with a pager as well?

Thanks!

I can add the the COUNT query Yii issues initially looks fine to me and indeed the numbers of results looks legit yet its the number of results in each page of the paginator which is awkward: both lowered than what I requested from it and also inconsistent and changes between the pages, as I page back and forth.

WTF?..

I think the problem is that LIMIT in combination with OUTER JOINS doesn’t make sense for 1:n rels. This is not a Yii problem but a “SQL query problem” in general. The question merely is why you can’t use lazy loading. It should be possible to restructure your query into 2 queries (one for the 1 model and 1 for the n models) so the LIMIT clause used by the paginator makes sense. Could you post your relations etc.?

yeah, this MySQL limitation is what I gathered from my research but I failed to understand what’s the story with this limitation and why does it exists? isn’t the LIMIT clause applied only after MySQL have built its resulted table?

And how can I restructure the code to make it 2 separate queries? Its all done by CActiveDataProvider in combination with CSort.

The queries that Yii produced are two in my case: one COUNT query and the only that actually fetches the data/rows needed. They are as follows:

BTW, to fulfill my requirements I had to craft the query of CDbCriteria a bit with addCondition(). That’s the reason for the ORs in the query.

COUNT query (I’ve changed some tables and columns names…):





SELECT

   COUNT(DISTINCT `t`.`id`)

FROM

   `main` `t`

LEFT OUTER JOIN

   `relating_table` `workingIn`

      ON (

 		`workingIn`.`main_id`=`t`.`id`

      )

WHERE

   (

      (

 		t.status='1'

      )

      AND (

 		(

            t.base_loc_country_id = 195

            OR

            workingIn.country_id = 195

 		)

      )

   )



SELECT query (table and col names altered to some degree and also I’ve trimmed out the long list of columns it fetches as its not relevant):





SELECT

   `t`.`id` AS `t0_c0`,

   /* more columns selected from main table... */

   `workingIn`.`id` AS `t1_c0`,

   /* more columns selected from relating_table... */

FROM

   `main` `t`

LEFT OUTER JOIN

   `relating_table` `workingIn`

      ON (

 		`workingIn`.`main_id`=`t`.`id`

      )

WHERE

   (

      (

 		t.status='1'

      )

      AND (

 		(

            t.base_loc_country_id = 195

            OR

            workingIn.country_id = 195

 		)

      )

   )

ORDER BY

   t.created_on DESC LIMIT 10



Hi Boaz and Haensel,

I also stumbled upon exactly the same problem before:

http://www.yiiframework.com/forum/index.php/topic/21781-relational-filter-and-pagination-with-has-many-or-many-many

In short, for a HAS_MANY relation, we are in a dilemma like this:

  • "together" => true

[list]

  • doing eager loading

  • needed for filtering by a column of the related table

[*]"together" => false

  • doing lazy loading

  • needed for correct pagination of the main table

[/list]

As for the problem of the item count in a page, the charts in the following post may help you understand the reason.

http://www.yiiframework.com/forum/index.php/topic/33756-bug-using-relation-and-pagination-in-a-findall-from-activerecord/page__view__findpost__p__162559

In eager loading, the number of the retrieved rows doesn’t match the number of AR objects.

A workaround for this problem might be using a sub query in the condition:




// Assuming Product HAS_MANY Options

/* 'options' => array(self::HAS_MANY, 'Option', 'product_id'); */


// We don't use AR relations

/* $criteria->with = 'options';

   $criteria->together = true; */


// We don't compare the related column like this

/* $criteria->compare('options.name', $this->option_name, true); */


// Instead, we construct a condition manually:

if ($this->option_name != '')

{

    $criteria->addCondition(

        'EXISTS(

            SELECT *

            FROM tbl_product pr

            INNER JOIN tbl_option op ON pr.id = op.product_id

            WHERE pr.id = t.id AND op.name LIKE :name

        )'

    );

    $criteria->params[':name'] = '%' . $this->option_name . '%';

}


// Or, also this will work

if ($this->option_name != '')

{

    $products = Yii::app()->db->createCommand()

        ->selectDistinct('id')

        ->from('tbl_product pr')

        ->join('tbl_option op', 'pr.id = op.product_id')

        ->where('op.name LIKE :name', array(':name' => '%' . $this->option_name . '%'))

        ->queryAll();

    $ids = array();

    foreach($products as $product)

        $ids[] = $product['id'];

    $criteria->addInCondition('t.id', $ids);

}



Note that we don’t join tbl_option to the main table (tbl_product t).

Ah, I see, makes sense. Thank you softark! So it really seems that a subquery is the only way to get the "right" row count. The only drawback could be performance now

Hi softark. Always good to have you on-board and this time is no exception. Heck, not even a CException!

I’ve tried your solution with the subquery and it works “as advertised”. Thanks!

I have to mention that I didn’t fully comprehend what’s MySQL or the SQL complain about using LIMIT on JOINed tables. My contemplation above is still valid but the most important thing is to get it to work. The Yii Dev team will need to break their heads on this (but I’ll be happy to fully comprehend the reasons as well).

Which brings me to the last issue - this thread would not be complete without letting the dev team know about this possible solution (and at last more information) on the opened issue on this. I’ve updated it.

Thanks, Boaz, for your “kind” words. :D

Indeed, it took me quite a long time to understand and accept this limitation regarding the relational filtering and the pagination of HAS_MANY related tables.

I don’t call it a bug, because it comes from the difference between sql queried result sets and AR objects. And I’m afraid that “won’t fix” might be the best solution for it on the framework level. I may be wrong, but it seems to me any workaround on the framework level could be unnecessarily fat, tricky and/or fragile.

One thing I could complain about Yii would be the lack of comprehensive documentation on the view widgets (CGridView and CListView) together with the data providers (CActiveDataProvider, CSqlDataProvider and CArrayDataProvider) and the related objects (CSort and CPagination).

These are the very best parts of Yii with their strength and convenience. We all love it.

But, did you experience some hard time understanding how they work together to produce a nice page showing the filtered and sorted list?

Yes, I did. And I believe many newcomers are experiencing the same old difficulty.

So, well, it’s not a promise but only a hope, but I’d like to write a wiki on this matter, maybe someday. :)

I wouldn’t either call it a bug but since 3 such issues are already created, and the one I replied on in the most recent, and still in ‘open’ status, I thought this could only help in providing the dev tem more information about what the author meant and possible solution.

The solution in the form of EXISTS subquery might be use-able in the framework level in the most simplest case but just like you, I’m afraid that the its either not scalable or like you stated - too fat, ugly and fragile. Exactly the opposite of KISS, and a solution that raises the complexity of the parts at hand too much.

There are some wiki articles and forum posts that handle this use case but indeed not a single article. Had my understanding of the reasons for the problem been better I would have written a wiki article myself. All in all I can’t complain much about Yii’s documentation and also lets remember its evolves constantly. I agree that this rather-niche-issue of “C*View classes + HAS_MANY related tables, searching on the related tables + pagination” could have been mentioned in the official docs.

Anyway, among the relevant Wiki articles:

http://www.yiiframework.com/wiki/280/1-n-relations-sometimes-require-cdbcriteria-together

http://www.yiiframework.com/wiki/281/searching-and-sorting-by-related-model-in-cgridview

There are also forum posts on the matter (like the one mentioned earlier in this thread) but I don’t have them at my disposal currently.