Active Record, get a single sql-query

Yii 1.1 General Discussion
1

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

2

Setting the join option seems to work:




   public function actionArtList()

   {

      $nCategory = isset( $_GET[ 'cat' ] ) ? $_GET[ 'cat' ] : 0;


      $dataProvider = new CActiveDataProvider( 'Article', array(

         'criteria' => array(

            'with' => array( 'manufacturer', 'category' ), 

            'join' => 'INNER JOIN artload_article2category ON artload_article2category.article_id = t.id

                INNER JOIN artload_category ON artload_category.id = artload_article2category.category_id

                LEFT JOIN artload_manufacturer ON artload_manufacturer.id = t.manufact_id',

            'condition' => 't.active=\'1\' and artload_category.id = ' . $nCategory,

         ),


         'pagination' => array( 'pageSize' => 10 )

      ));

      

      $this->render( 'artlist' ,array(

         'dataProvider' => $dataProvider,

      ));

   }