Hi, i’m using CActiveDataProvider to fetch objects from DB.
the code is:
$menus=new CActiveDataProvider('MenuItem', array(
'criteria'=>array(
'with'=>array('plans' => array(
'condition'=>'plan.startDate < '.time().' and plan.endDate > '.time(),
'order'=>'plan.priority desc',
'joinType'=>'INNER JOIN',
)),
'order'=>'t.menuItemID desc',
),
'pagination'=>array(
'pageSize'=>2,
),
));
the relationship between objects are:
MenuItem & plans:
‘plans’ => array(self::HAS_MANY, ‘MenuItemPlan’, ‘menuItemID’, ‘with’=>‘plan’),
MenuItemPlan & plan:
‘plan’=>array(self::BELONGS_TO, ‘MenuPlan’, ‘menuPlanID’, ‘joinType’=>‘INNER JOIN’),
the runtime log:
Query1:
SELECT COUNT(DISTINCT `t`.`menuItemID`) FROM `menu_item` `t`
INNER JOIN `menu_item_plan` `plans` ON
(`plans`.`menuItemID`=`t`.`menuItemID`) INNER JOIN `menu_plan` `plan` ON
(`plans`.`menuPlanID`=`plan`.`menuPlanID`) WHERE (plan.startDate <
1299677950 and plan.endDate > 1299677950)
Query2:
SELECT `t`.`menuItemID` AS `t0_c0`, `t`.`vendorID` AS
`t0_c1`, `t`.`menuName` AS `t0_c2`, `t`.`menuPic` AS `t0_c3`, `t`.`status`
AS `t0_c4`, `t`.`addedDate` AS `t0_c5`, `t`.`modifiedDate` AS `t0_c6`,
`t`.`description` AS `t0_c7` FROM `menu_item` `t` ORDER BY t.menuItemID
desc LIMIT 2
Query3:
SELECT `t`.`menuItemID` AS `t0_c0`, `plans`.`menuItemPlan` AS
`t1_c0`, `plans`.`menuPlanID` AS `t1_c1`, `plans`.`menuItemID` AS `t1_c2`,
`plans`.`planedQuantity` AS `t1_c3`, `plans`.`price` AS `t1_c4`,
`plan`.`menuPlanID` AS `t2_c0`, `plan`.`startDate` AS `t2_c1`,
`plan`.`endDate` AS `t2_c2`, `plan`.`priority` AS `t2_c3`,
`plan`.`createTime` AS `t2_c4`, `plan`.`vendorID` AS `t2_c5`,
`plan`.`title` AS `t2_c6` FROM `menu_item` `t` INNER JOIN `menu_item_plan`
`plans` ON (`plans`.`menuItemID`=`t`.`menuItemID`) INNER JOIN `menu_plan`
`plan` ON (`plans`.`menuPlanID`=`plan`.`menuPlanID`) WHERE
(`t`.`menuItemID` IN ('14', '13')) AND (plan.startDate < 1299677950 and
plan.endDate > 1299677950) ORDER BY plan.priority desc
and i log getItemCount and getTotalItemCount of CActiveDataProvider:
we got menus in current page:2
we got menus total:3
I confirm that Query3 returns only 1 row as I run it directly against MySQL server.
the question is: I should only get 1 item in current page as Query3 indicates. why I get 2 items for this page? (and 2 more on the next page)
and more confused if I change to show 4 items per page:
we got menus in current page:4
we got menus total:3
the items in current page are more than the total!
Yii version: yii-1.1.6.r2877