CActiveDataProvider and criteria


<div id="site_container">    

        <div id="add_cat"><?php echo CHtml::link('Создать сайт',array('site/create')) ?></div>

    <?php

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

                'criteria'=>array(

                        'join'=>'LEFT JOIN theme ON theme.id = site.theme_id INNER JOIN hosting ON hosting.id = site.hosting_id',

                        'select'=>'site.name, theme.name as theme, site.domain, hosting.expiration, site.c_page, site.c_all_page',

        ),

        ));


                $this->widget('zii.widgets.grid.CGridView', array(

                    'dataProvider'=>$dataProvider                   

                ));

    ?>    

</div>

Error: CDbCommand failed to execute the SQL statement: SQLSTATE[42S22]: Column not found: 1054 Unknown column ‘site.theme_id’ in ‘on clause’

SQL :

SELECT site.name, theme.name as theme, site.domain, hosting.expiration, site.c_page, site.c_all_page

FROM site

LEFT JOIN theme ON theme.id = site.theme_id

INNER JOIN hosting ON hosting.id = site.hosting_id

Please help cusmize criteria setting for this sql

Site.php - Model


<?php

/**

 * Description of Site

 *

 * @author like2dev

 */

class Site extends CActiveRecord {    	

	

    public static function model($className = __CLASS__) {

        return parent::model($className);        

    }

    

    public function tableName() {

        return 'site';

    }

    

    public function attributeLabels() {

    	return array(

    		'id'=>'Номер',

    		''=>'',

    		''=>'',

    		''=>'',

    		''=>'',	

    	);

    } 

}    



ER Diagramm^

http://s45.radikal.ru/i109/1004/ff/1b1a3c193de1.png

Try to replace ‘site.’ with ‘t.’ as t is used as alias for the main table.

Hi,

i’m wondering why not let yii do all this stuff instead of writing it by yourself?!

Instead of the ‘join’ property in criteria you could add this as a relation to your model so you can use it again everywhere else:




/**

* @return array relational rules.

*/

public function relations()

{

  return array(

  'hosting'=>array(self::BELONGS_TO, 'hosting', 'hosting_id', 'joinType'=>'INNER JOIN'),

  'theme'=>array(self::BELONGS_TO, 'theme', 'theme_id'),

  'pages'=>array(self::HAS_MANY, 'page', 'site_id'),

  // .... and so on

 );

}

then it would simply look s.th. like this:


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

                'criteria'=>array(

                      'with'=>array('theme'=>array(

                                        'select'=>'name AS theme',

                                    ),

                                 'hosting'=>array(

                                     'select'=>'expiration',

                                 ),

                              //... and so on

                      ),

                )



Regards