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?