Pagination With Many-To-Many

Yii 1.1 General Discussion
1

I have 2 models: Person and Job , connected by many-to-many

The jobs of every person are show in Person’s cgridView. So far , no problems.

However , in order to be able to filter the table by jobs , I had to add these lines in the Person model in the search function:


$criteria->compare('tblJobs.type',$this->job_search);

                $criteria->with=array('tblJobs');

                $criteria->together=true;

The filter is working well , but then I ran into a pagination problem: if I have 2 or less people in a page , the page is not seen. It writes:" viewing 8 out of 10 results" but no 2nd page available containing the other 2.

Any ideas why is that so?

2

AFAIR count() query used for pagination does not take relations into account, so it shows you the total number of rows (w/o filtering)

Enable the debug panel and see how does SELECT COUNT(*) actually look like.

3

You mean the debug panel extension?

4

yes.

5

When you are working with a HAS_MANY or MANY_MANY relation, you are very likely to face this dilemma:

  1. When you want to filter by a column in the relation, you have to eager load it with ‘together’ set to true. Otherwise you’ll get ‘column not found’ error.

  2. When you want to paginate the main model correctly, you must set ‘together’ to false. Otherwise the ‘LIMIT’ works on the joined tables as a whole (i.e., not on the main table alone), and the count of the main models fetched by the query may be smaller than the limit.

For example:




select * from a join b on a.id = b.a_id

where a.name like 'foo%'

limit 5

The query above may bring back these records:




a.id   a.name   b.id   b.a_id   b.name

1      foo      1      1        bar

1      foo      2      1        bar2

1      foo      3      1        bar3

2      foo2     4      2        bar4

2      foo2     5      2        bar5

So, we do get 5 records, but only 2 instances of the main model.

Please take a look at the following wikis

http://www.yiiframework.com/wiki/527/relational-query-lazy-loading-and-eager-loading-with-and-together

http://www.yiiframework.com/wiki/428/drills-search-by-a-has_many-relation

1 Like
6

Great articles!

I’m almost there. I’v managed to make the pagination and filter work by grouping the Person’s id with the following code:


$criteria->compare('tblJobs.type',$this->job_search);

                $criteria->with=array('tblJobs');

				$criteria->group='t.id';

                $criteria->together=true;

However now , in the cGridView , only one job is being shown for a person , even if he has more.

1 Like
7

I recommend that you check my extension: www.yiiframework.com/extension/relatedsearchbehavior/ which is very usefull for this.