Hello,
I have a code like this:
$criteria = array(
'select' => 'id,company,url,logo,logo_width,logo_height,intro',
'condition' => 'account_status = "1" AND campaign_status = "1"',
'order' => 'company',
);
if (isset($_GET['category']) && is_numeric($_GET['category']) && $_GET['category'] != 0) {
$criteria['with']['company_accounts_categories'] = array(
'joinType' => 'JOIN',
'condition' => 'category_id=:category_id',
'params' => array(':category_id' => $_GET['category']),
);
}
$dataProvider = new CActiveDataProvider('CompanyAccounts', array(
'criteria' => $criteria,
'pagination' => array(
'pageSize' => 20,
'currentPage' => $page - 1,
),
));
The COUNT part is ok:
SELECT COUNT(DISTINCT `t`.`id`) FROM `company_accounts` `t`
JOIN `company_accounts_categories` `company_accounts_categories` ON
(`company_accounts_categories`.`company_id`=`t`.`id`) WHERE
(account_status = "1" AND campaign_status = "1") AND
(category_id=:category_id)
But the select part is really messed up !
Instead of doing the join in one query…
1st it does this:
SELECT `t`.`id` AS `t0_c0`, `t`.`company` AS `t0_c3`,
`t`.`url` AS `t0_c7`, `t`.`logo` AS `t0_c10`, `t`.`logo_width` AS `t0_c11`,
`t`.`logo_height` AS `t0_c12`, `t`.`intro` AS `t0_c13` FROM
`company_accounts` `t` WHERE (account_status = "1" AND campaign_status =
"1") ORDER BY company LIMIT 20
Then it does this:
SELECT `t`.`id` AS `t0_c0`,
`company_accounts_categories`.`company_id` AS `t1_c0`,
`company_accounts_categories`.`category_id` AS `t1_c1`,
`company_accounts_categories`.`order` AS `t1_c2` FROM `company_accounts`
`t` JOIN `company_accounts_categories` `company_accounts_categories` ON
(`company_accounts_categories`.`company_id`=`t`.`id`) WHERE (`t`.`id` IN
('1280', '903', '839', '520', '1496', '1465', '378', '1430', '1167', '48',
'1551', '319', '1373', '1460', '123', '385', '1535', '1521', '1696',
'447')) AND (category_id=:category_id)
Am I doing something wrong ?!