MANY_MANY and Pagination

While trying to paginate a query including a join to a MANY_MANY table, the pages are showing incoherent results on specific conditions.

Consider 2 tables ( user and activity ) linked through a MANY_MANY relation (3rd table).

So a user can have many activities and activities can have many users.




            $criteria=new CDbCriteria;

            $criteria->distinct =  true;

            $join = array ( 'activities' => array('condition'=>'activity_id_ in (1,2,3) ', ));

            //-- Pagination

            $pages=new CPagination( user::model()->with($join)->count($criteria));

            $pages->pageSize=self::PAGE_SIZE;

            $pages->applyLimit($criteria);

            //-- Data and render

            $models = user::model()->with($join)->together()->findAll($criteria);

            $this->render('search',array('models'=>$models, 'pages'=>$pages,));



Two SQL statements are generated:

[sql]

SELECT DISTINCT COUNT(DISTINCT t.user_id_) FROM user t

LEFT OUTER JOIN user_has_activity activities_activities ON

(t.user_id_=activities_activities.user_) LEFT OUTER JOIN activity

activities ON (activities.activity_id_=activities_activities.activity_) WHERE (activity_id_ in (1,2,3))

[/sql]

This returns the number of “distinct” users matching activities 1,2 and 3.

This is used to set pagination.

Then the records to display:

[sql]

SELECT DISTINCT t.user_id_ AS t0_c0, t.name_ AS

t0_c1, t.address_ AS t0_c2, t.town_ AS t0_c3, t.zip_ AS

t0_c4, t.siret_ AS t0_c5, t.email_ AS t0_c6, t.phone_ AS

t0_c7, t.cellular_ AS t0_c8, t.fax_ AS t0_c9,

activities.activity_id_ AS t1_c0, activities.name_ AS t1_c1,

activities.category_ AS t1_c2 FROM user t LEFT OUTER JOIN

user_has_activity activities_activities ON

(t.user_id_=activities_activities.user_) LEFT OUTER JOIN activity

activities ON

(activities.activity_id_=activities_activities.activity_) WHERE (activity_id_ in (1,2,3)) LIMIT 2 OFFSET 2

[/sql]

If all users have links on activities 1,2,3, the SQL returns 3 records per user, i.e. 3 times the number of records calculated by the count and used to paginate.

Depending on PAGE_SIZE, some records will miss or be duplicated on 2 pages.

Then, is it possible to prevent the tables from the join to be part of the select?

Remove the ‘together’ parameter - that is what is forcing duplicate rows.