Column not found: 1054 Unknown column

Yii 1.1 General Discussion
1

CDbCommand failed to execute the SQL statement: SQLSTATE[42S22]: Column not found: 1054 Unknown column ‘CategoryId’ in ‘where clause’

‘Daytrip’ Model:


    public function relations() {

        return array(

            'rCity' => array(self::BELONGS_TO, 'City', 'CityId'),

            'rCategory' => array(self::MANY_MANY, 'Category', 'DaytripCategory(DaytripId, CategoryId)',

                'order' => 'CategoryId'

            ),

            'rTag' => array(self::MANY_MANY, 'Tag', 'DaytripTag(DaytripId, TagId)',

                'order' => 'Tag'

            )

        );

    }

Controller:


    public function actionTest() {

	Daytrip::model()->mapToLatLon(100, 72);

	$lat = Daytrip::model()->Latitude;

	$lon = Daytrip::model()->Longitude;


	$criteria=new CDbCriteria(array(

			'condition'=>'Active = 1 AND rCategory.IdCategory IN (1,3,4)',

			'order'=>'Distance ASC, Priority DESC, RAND()',

			'with'=>array('rCity', 'rCategory', 'rTag' => array('order'=>'Tag')),

	));


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

	    'criteria'=>$criteria,

	));


	$this->render('test', array('data' => $dataProvider));

    }

Database:

TABLE Daytrips FIELDS IdDaytrip, Daytrip, Street, Number, ZipCode, Website, Active

TABLE DaytripCategory FIELDS DaytripId, CategoryId

TABLE Categories FIELDS IdCategory, Category

2

[s]My guess is that you developed the db on Windows and imported the SQL dump into a db on Linux.

The column names for MySql on most Linux platforms are converted to lowercase by default, therefore your models can no longer find the columns in your db.

I recommend using lowercase for all column names, seperated by an underscore where you might use camelcase for clarity.[/s]

Hmm, maybe not, is that just table names? …sorry not by my puter.

3

It’s CategoryId or IdCategory ?

In daytrip model you have rCategory.CategoryId and in the controler criteria condition you have rCategory.IdCategory…

4

rCategory.CategoryId or rCategory.IdCategory doesn’t make any difference.

i changed the condition to:


        $criteria = new CDbCriteria(array(

			'with' => array(

			    'rCity',

			    'rCategory',

			    'rTag' => array('order'=>'Tag')

			),

			'condition' => 'Active = 1 AND rCategory.CategoryId IN (2)',

                        'order' => 'Priority DESC, RAND()',

			'limit' => 100

        ));

Now i get the query below, but you can see it’s not joining the Category table…


SELECT `t`.`IdDaytrip` AS `t0_c0`, `t`.`Daytrip` AS

`t0_c1`, `t`.`ContactPerson` AS `t0_c2`, `t`.`Street` AS `t0_c3`,

`t`.`Number` AS `t0_c4`, `t`.`CityId` AS `t0_c5`, `t`.`ZipCode` AS `t0_c6`,

`t`.`Phone` AS `t0_c7`, `t`.`Fax` AS `t0_c8`, `t`.`Email` AS `t0_c9`,

`t`.`Website` AS `t0_c10`, `t`.`YouTube` AS `t0_c11`, `t`.`Priority` AS

`t0_c12`, `t`.`Modified` AS `t0_c13`, `t`.`Active` AS `t0_c14`,

`t`.`Status` AS `t0_c15`, `rCity`.`IdCity` AS `t1_c0`, `rCity`.`CountryId`

AS `t1_c1`, `rCity`.`Province` AS `t1_c2`, `rCity`.`Municipality` AS

`t1_c3`, `rCity`.`City` AS `t1_c4`, `rCity`.`Deeplink` AS `t1_c5`,

`rCity`.`Latitude` AS `t1_c6`, `rCity`.`Longitude` AS `t1_c7`,

`rCity`.`GoogleMatch` AS `t1_c8`

FROM `Daytrips` `t`

LEFT OUTER JOIN `Cities` `rCity` ON (`t`.`CityId`=`rCity`.`IdCity`)

WHERE (

    Active = 1

    AND rCategory.CategoryId IN (2)

)

ORDER BY Priority DESC, RAND()

LIMIT 100

EDIT: full log: http://paste2.org/p/900766

5

nobody?