Yii Framework Forum: Column not found: 1054 Unknown column - Yii Framework Forum

Jump to content

Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

Column not found: 1054 Unknown column CDbCommand failed to execute the SQL statement: Rate Topic: -----

#1 User is offline   bas_vdl 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 215
  • Joined: 01-April 09

Posted 30 June 2010 - 08:55 AM

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
0

#2 User is offline   outrage 

  • Advanced Member
  • PipPipPip
  • Yii
  • Group: Members
  • Posts: 312
  • Joined: 10-November 09
  • Location:Blackpool, United Kingdom

Posted 30 June 2010 - 05:28 PM

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.


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

#3 User is offline   Maurizio Domba Cerin 

  • Yii - Yesss It Is !!!
  • Yii
  • Group: Yii Dev Team
  • Posts: 4,317
  • Joined: 12-October 09
  • Location:Croatia

Posted 01 July 2010 - 02:02 AM

It's CategoryId or IdCategory ?

In daytrip model you have rCategory.CategoryId and in the controler criteria condition you have rCategory.IdCategory...
Find more about me.... btw. Do you know your WAN IP?
0

#4 User is offline   bas_vdl 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 215
  • Joined: 01-April 09

Posted 01 July 2010 - 07:30 AM

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
0

#5 User is offline   bas_vdl 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 215
  • Joined: 01-April 09

Posted 05 July 2010 - 03:21 AM

nobody?
0

Share this topic:


Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

1 User(s) are reading this topic
0 members, 1 guests, 0 anonymous users