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?