Hi, I’m new to yii and have a maybe simple question. I have 3 tables which I want to join. How do I get yii to generate a single sql-query?
Model:
public function relations()
{
return array(
'category' => array( self::MANY_MANY, 'Category',
'artload_article2category(article_id, category_id)',
'joinType' => 'INNER JOIN',
),
// 'article' => array( self::HAS_MANY, 'Article', 'parent_id' ),
'manufacturer' => array( self::BELONGS_TO, 'Manufacturer', 'manufact_id' ),
'categoryFilter' => array( self::MANY_MANY, 'Category',
'artload_article2category(article_id, category_id)',
'condition' => 'categoryFilter.id = (:category_id)',
'joinType' => 'INNER JOIN'
)
);
}
Controller:
public function actionArtList()
{
$dataProvider = new CActiveDataProvider( 'Article', array(
'criteria' => array(
'with' => array( 'category', 'manufacturer' ),
'together' => true,
'condition' => 't.active=\'1\'',
),
'pagination' => array( 'pageSize' => 10 )
));
$this->render( 'artlist' ,array( 'dataProvider' => $dataProvider ));
}
The pagination shows ‘Displaying 1-2 of 0 result(s)’.
The ListView shows 2 items.
The sql-query to get the total amount of records is ok:
2010/01/23 14:34:30 [trace] [system.db.CDbCommand] Querying SQL: SELECT COUNT(DISTINCT `t`.`id`) FROM `artload_article` `t` INNER JOIN `artload_article2category` `category_category` ON (`t`.`id`=`category_category`.`article_id`) INNER JOIN `artload_category` `category` ON (`category`.`id`=`category_category`.`category_id`) LEFT OUTER JOIN `artload_manufacturer` `manufacturer` ON (`t`.`manufact_id`=`manufacturer`.`id`) WHERE (t.active='1')
These two queries should be one:
2010/01/23 14:34:30 [trace] [system.db.CDbCommand] Querying SQL: SELECT `t`.`id` AS `t0_c0`, `t`.`parent_id` AS `t0_c1`, `t`.`active` AS `t0_c2`, `t`.`sorting` AS `t0_c3`, `t`.`sync` AS `t0_c4`, `t`.`del` AS `t0_c5`, `t`.`art_nr` AS `t0_c6`, `t`.`name` AS `t0_c7`, `t`.`manufact_id` AS `t0_c8`, `t`.`se_tag` AS `t0_c9`, `t`.`text_small` AS `t0_c10`, `t`.`text_large` AS `t0_c11`, `t`.`text_html` AS `t0_c12`, `t`.`price` AS `t0_c13`, `t`.`price_org` AS `t0_c14`, `t`.`price_from` AS `t0_c15`, `t`.`delivery` AS `t0_c16`, `t`.`image` AS `t0_c17`, `t`.`on_home` AS `t0_c18`, `t`.`on_home_text` AS `t0_c19`, `t`.`variant_name` AS `t0_c20`, `t`.`variant_value` AS `t0_c21`, `t`.`created` AS `t0_c22`, `t`.`modified` AS `t0_c23`, `manufacturer`.`id` AS `t2_c0`, `manufacturer`.`name` AS `t2_c1`, `manufacturer`.`created` AS `t2_c2`, `manufacturer`.`modified` AS `t2_c3` FROM `artload_article` `t` LEFT OUTER JOIN `artload_manufacturer` `manufacturer` ON (`t`.`manufact_id`=`manufacturer`.`id`) WHERE (t.active='1') LIMIT 10
2010/01/23 14:34:30 [trace] [system.db.CDbCommand] Querying SQL: SELECT `t`.`id` AS `t0_c0`, `category`.`id` AS `t1_c0`, `category`.`parent_id` AS `t1_c1`, `category`.`active` AS `t1_c2`, `category`.`sorting` AS `t1_c3`, `category`.`name` AS `t1_c4`, `category`.`description` AS `t1_c5`, `category`.`se_tag` AS `t1_c6`, `category`.`created` AS `t1_c7`, `category`.`modified` AS `t1_c8` FROM `artload_article` `t` INNER JOIN `artload_article2category` `category_category` ON (`t`.`id`=`category_category`.`article_id`) INNER JOIN `artload_category` `category` ON (`category`.`id`=`category_category`.`category_id`) WHERE (`t`.`id` IN (1962, 1963))
Thanks in advance,
Ralf