Odd view problem

Let me start of by saying that I am not a noob to PHP or MySQL, however I am a complete noob when it comes to Yii and frameworks in general. I have been playing around with it for the past few days and so far like it a lot.

I went through the tutorials and now I am in the process of building my first test application to try to get a better handle of things.

I am having a strange problem with my user view. I keep getting the following error…

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

Here is the SQL query that is causing that error:


Error in querying SQL: SELECT `community`.`id` AS `t1_c0`,

`community`.`locationID` AS `t1_c1`, `community`.`name` AS `t1_c2`,

`community`.`description` AS `t1_c3`, `community`.`features` AS `t1_c4`,

`community`.`directions` AS `t1_c5`, `community`.`phone` AS `t1_c6`,

`community`.`salesPerson` AS `t1_c7`, `community`.`email` AS `t1_c8`,

`community`.`logoURL` AS `t1_c9`, `community`.`monumentURL` AS `t1_c10`,

`community`.`imageURL1` AS `t1_c11`, `community`.`imageURL2` AS `t1_c12`,

`community`.`imageURL3` AS `t1_c13`, `community`.`platURL` AS `t1_c14`,

`community`.`mapURL` AS `t1_c15`, `community`.`deleted` AS `t1_c16`,

`community`.`active` AS `t1_c17` FROM `community` `community`  WHERE

(`community`.`id'`=:ypl0)

in C:\xampp\htdocs\YiiTutorial\framework\zii\widgets\CDetailView.php (201)

in C:\xampp\htdocs\BuilderApp\public_html\protected\views\user\view.php

(29)

in

C:\xampp\htdocs\BuilderApp\public_html\protected\controllers\UserController.php

(55)

I ran this query outside of Yii, and I received the same error. It took me forever to notice the extra [color="#FF0000"][size=“4”]’[/size][/color] (single quotation mark) after community.id in the where clause. When I removed the extra [color="#FF0000"][size=“4”]’[/size][/color] the query ran with no problems. So I’m pretty sure the [color="#FF0000"][size=“4”]’[/size][/color] is the cause of the error, but I have no idea why it’s there or where it’s coming from.

All I did to get this error was change the original user view from




<h1>View User: <?php echo $model->firstName." ".$model->lastName; ?></h1>


<?php $this->widget('zii.widgets.CDetailView', array(

	'data'=>$model,

	'attributes'=>array(

		'username',

		'password',

		'email',

		'communityID',

		'lotNumber',

		'userLevelID',

		'deleted',

	),

)); ?>



to…


<h1>View User: <?php echo $model->firstName." ".$model->lastName; ?></h1>


<?php $this->widget('zii.widgets.CDetailView', array(

	'data'=>$model,

	'attributes'=>array(

		'username',

		'password',

		'email:email',

		'community.name',

		'lotNumber',

		'userLevel.description',

		'deleted',

	),

)); ?>



The relations in the User model is


	public function relations()

	{

		// NOTE: you may need to adjust the relation name and the related

		// class name for the relations automatically generated below.

		return array(

			'community' => array(self::BELONGS_TO, 'Community', 'communityID'),

			'userLevel' => array(self::BELONGS_TO, 'UserLevel', 'userLevelID'),

		);

	}



Is there something I’m overlooking? I’m sure it’s probably something very simple. I have done this sort of thing with other models and views with no issues. In fact, if change community.name back to communityID but leave userLevel.description, it works just fine.

Thanks in advance for helping out this noob. :D

Did you mean ‘email.email’?

/Tommy

No. That represents the type of attribute so it displays the email address as a mailto link. But, I’m sure you were just making sure of that.

Yeah, I’ve forgot about that syntax mentioned in the class reference.

Your description altogether doesn’t make sense to me. But since there’s some problem with the WHERE clause i would point you to the loadModel() method in the controller. Things to try: remove the int cast, use (try) find() instead of findByPk. Any difference?

BTW What Yii version do you use and which DBM?

I think I’ve seen a related issue somewhere (not too long ago) but can’t find it now. Obviously you use 1.1.x since you get the relationship generated for you.

/Tommy

yeah the problem is in WHERE(community.id'=:ypl0). If you notice, it says community.id' and it should say community.id. For some reason there is an extra [size=“4”][/size] (single quote) added right after id.

I using Yii 1.1.4 and MySQL 5.1.41.

I’ll try your suggestion and see if it helps.

I tried your suggestion, but there was no difference.

That’s weird since IIRC find() should not generate a WHERE clause (it returns the first record so probably LIMIT 1 is used).

BTW the single quote is inside the backticks (they are not single quotes).

Just to be sure also try eager loading, like this User::model()->with(‘community’, ‘userLevel’)->find()

(But you didn’t mention a possible non-existent object/unknown attribute kind of error)

/Tommy

Still get the same error and the single quote is still there.

It’s a good thing I shave my head, or I would be pulling all my hair out right now! :P

I assumed you use the code Gii generated, obviously for the User model. Since you have a CDetailView I assumed the action named view and the query in loadModel(). This now seems to have been an erroneous assumption. You have to tell us more about which action you call and show the statement that generates the query. Perhaps you modified something more than the view columns?

/Tommy

Yes, you assume correctly. I did use the code that Gii generated. I don’t remember altering anything other than what I mentioned in my first post, but it’s possible I have.

Here are the User model, view, and controller files in their entirety…

UserController:


<?php


class UserController extends Controller

{

	/**

	 * @var string the default layout for the views. Defaults to '//layouts/column2', meaning

	 * using two-column layout. See 'protected/views/layouts/column2.php'.

	 */

	public $layout='//layouts/column2';


	/**

	 * @return array action filters

	 */

	public function filters()

	{

		return array(

			'accessControl', // perform access control for CRUD operations

		);

	}


	/**

	 * Specifies the access control rules.

	 * This method is used by the 'accessControl' filter.

	 * @return array access control rules

	 */

	public function accessRules()

	{

		return array(

			array('allow',  // allow all users to perform 'index' and 'view' actions

				'actions'=>array('index','view'),

				'users'=>array('*'),

			),

			array('allow', // allow authenticated user to perform 'create' and 'update' actions

				'actions'=>array('create','update'),

				'users'=>array('@'),

			),

			array('allow', // allow admin user to perform 'admin' and 'delete' actions

				'actions'=>array('admin','delete'),

				'users'=>array('admin'),

			),

			array('deny',  // deny all users

				'users'=>array('*'),

			),

		);

	}


	/**

	 * Displays a particular model.

	 * @param integer $id the ID of the model to be displayed

	 */

	public function actionView($id)

	{

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

			'model'=>$this->loadModel($id),

		));

	}


	/**

	 * Creates a new model.

	 * If creation is successful, the browser will be redirected to the 'view' page.

	 */

	public function actionCreate()

	{

		$model=new User;


		// Uncomment the following line if AJAX validation is needed

		// $this->performAjaxValidation($model);


		if(isset($_POST['User']))

		{

			$model->attributes=$_POST['User'];

			if($model->save())

				$this->redirect(array('view','id'=>$model->id));

		}


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

			'model'=>$model,

		));

	}


	/**

	 * Updates a particular model.

	 * If update is successful, the browser will be redirected to the 'view' page.

	 * @param integer $id the ID of the model to be updated

	 */

	public function actionUpdate($id)

	{

		$model=$this->loadModel($id);


		// Uncomment the following line if AJAX validation is needed

		// $this->performAjaxValidation($model);


		if(isset($_POST['User']))

		{

			$model->attributes=$_POST['User'];

			if($model->save())

				$this->redirect(array('view','id'=>$model->id));

		}


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

			'model'=>$model,

		));

	}


	/**

	 * Deletes a particular model.

	 * If deletion is successful, the browser will be redirected to the 'index' page.

	 * @param integer $id the ID of the model to be deleted

	 */

	public function actionDelete($id)

	{

		if(Yii::app()->request->isPostRequest)

		{

			// we only allow deletion via POST request

			$this->loadModel($id)->delete();


			// if AJAX request (triggered by deletion via admin grid view), we should not redirect the browser

			if(!isset($_GET['ajax']))

				$this->redirect(isset($_POST['returnUrl']) ? $_POST['returnUrl'] : array('admin'));

		}

		else

			throw new CHttpException(400,'Invalid request. Please do not repeat this request again.');

	}


	/**

	 * Lists all models.

	 */

	public function actionIndex()

	{

		$dataProvider=new CActiveDataProvider('User');

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

			'dataProvider'=>$dataProvider,

		));

	}


	/**

	 * Manages all models.

	 */

	public function actionAdmin()

	{

		$model=new User('search');

		$model->unsetAttributes();  // clear any default values

		if(isset($_GET['User']))

			$model->attributes=$_GET['User'];


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

			'model'=>$model,

		));

	}


	/**

	 * Returns the data model based on the primary key given in the GET variable.

	 * If the data model is not found, an HTTP exception will be raised.

	 * @param integer the ID of the model to be loaded

	 */

	public function loadModel($id)

	{

		$model=User::model()->findByPk((int)$id);

		if($model===null)

			throw new CHttpException(404,'The requested page does not exist.');

		return $model;

	}


	/**

	 * Performs the AJAX validation.

	 * @param CModel the model to be validated

	 */

	protected function performAjaxValidation($model)

	{

		if(isset($_POST['ajax']) && $_POST['ajax']==='user-form')

		{

			echo CActiveForm::validate($model);

			Yii::app()->end();

		}

	}

}



User View:


<?php

$this->breadcrumbs=array(

	'Users'=>array('index'),

	$model->id,

);


$this->menu=array(

	array('label'=>'List User', 'url'=>array('index')),

	array('label'=>'Create User', 'url'=>array('create')),

	array('label'=>'Update User', 'url'=>array('update', 'id'=>$model->id)),

	array('label'=>'Delete User', 'url'=>'#', 'linkOptions'=>array('submit'=>array('delete','id'=>$model->id),'confirm'=>'Are you sure you want to delete this item?')),

	array('label'=>'Manage User', 'url'=>array('admin')),

);

?>


<h1>View User: <?php echo $model->firstName." ".$model->lastName; ?></h1>


<?php $this->widget('zii.widgets.CDetailView', array(

	'data'=>$model,

	'attributes'=>array(

		'username',

		'password',

		'email:email',

		'community.id',

		'lotNumber',

		'userLevel.description',

		'deleted',

	),

)); ?>



User model:


<?php


/**

 * This is the model class for table "user".

 *

 * The followings are the available columns in table 'user':

 * @property string $id

 * @property string $username

 * @property string $password

 * @property string $firstName

 * @property string $lastName

 * @property string $email

 * @property string $communityID

 * @property string $lotNumber

 * @property string $userLevelID

 * @property string $deleted

 *

 * The followings are the available model relations:

 * @property Community $community

 * @property UserLevel $userLevel

 */

class User extends CActiveRecord

{

	/**

	 * Returns the static model of the specified AR class.

	 * @return User the static model class

	 */

	public static function model($className=__CLASS__)

	{

		return parent::model($className);

	}


	/**

	 * @return string the associated database table name

	 */

	public function tableName()

	{

		return 'user';

	}


	/**

	 * @return array validation rules for model attributes.

	 */

	public function rules()

	{

		// NOTE: you should only define rules for those attributes that

		// will receive user inputs.

		return array(

			array('username, password, communityID, lotNumber, userLevelID', 'length', 'max'=>10),

			array('firstName, lastName', 'length', 'max'=>20),

			array('email', 'length', 'max'=>50),

			array('email', 'email'),

			// The following rule is used by search().

			// Please remove those attributes that should not be searched.

			array('id, username, password, firstName, lastName, communityID, lotNumber, userLevelID, deleted', 'safe', 'on'=>'search'),

		);

	}


	/**

	 * @return array relational rules.

	 */

	public function relations()

	{

		// NOTE: you may need to adjust the relation name and the related

		// class name for the relations automatically generated below.

		return array(

			'community' => array(self::BELONGS_TO, 'Community', 'communityID'),

			'userLevel' => array(self::BELONGS_TO, 'UserLevel', 'userLevelID'),

		);

	}


	/**

	 * @return array customized attribute labels (name=>label)

	 */

	public function attributeLabels()

	{

		return array(

			'id' => 'ID',

			'username' => 'Username',

			'password' => 'Password',

			'firstName' => 'First Name',

			'lastName' => 'Last Name',

			'email' => 'Email Address',

			'communityID' => 'Community',

			'lotNumber' => 'Lot Number',

			'userLevelID' => 'User Level',

			'deleted' => 'Deleted',

		);

	}


	/**

	 * Retrieves a list of models based on the current search/filter conditions.

	 * @return CActiveDataProvider the data provider that can return the models based on the search/filter conditions.

	 */

	public function search()

	{

		// Warning: Please modify the following code to remove attributes that

		// should not be searched.


		$criteria=new CDbCriteria;


		$criteria->compare('id',$this->id,true);

		$criteria->compare('username',$this->username,true);

		$criteria->compare('password',$this->password,true);

		$criteria->compare('firstName',$this->firstName,true);

		$criteria->compare('lastName',$this->lastName,true);

		$criteria->compare('email',$this->email,true);

		$criteria->compare('communityID',$this->communityID,true);

		$criteria->compare('lotNumber',$this->lotNumber,true);

		$criteria->compare('userLevelID',$this->userLevelID,true);

		$criteria->compare('deleted',$this->deleted,true);


		return new CActiveDataProvider(get_class($this), array(

			'criteria'=>$criteria,

		));

	}

}

Is this the information you require?

I get a query like this with 1.1.4-generated code


SELECT * FROM `tablename` `t` WHERE `t`.`tablename`='1' LIMIT 1

[s]Note the ‘t’ alias. The t1/t2 aliases in your query may come from an older version (not completely sure).

[/s]

Is it the right logfile you are looking in?

What is the exact error message?

Edit: Also try with(…)->together()->find…(…) (I always do). Obviously you get more than one query. I will try adding a related attribute to my newly generated view to see for myself

/Tommy

I have it set up so it shows the Application Log at the bottom of the page.

This is what it shows:


Description


CDbCommand failed to execute the SQL statement: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'community.id'' in 'where clause'

Source File


C:\xampp\htdocs\YiiTutorial\framework\web\helpers\CHtml.php(1664)


01652:      * "$model['author']['firstName']".

01653:      * @param mixed the model. This can be either an object or an array.

01654:      * @param string the attribute name (use dot to concatenate multiple attributes)

01655:      * @param mixed the default value to return when the attribute does not exist

01656:      * @return mixed the attribute value

01657:      * @since 1.0.5

01658:      */

01659:     public static function value($model,$attribute,$defaultValue=null)

01660:     {

01661:         foreach(explode('.',$attribute) as $name)

01662:         {

01663:             if(is_object($model))

01664: $model=$model->$name;

01665:             else if(is_array($model) && isset($model[$name]))

01666:                 $model=$model[$name];

01667:             else

01668:                 return $defaultValue;

01669:         }

01670:         return $model;

01671:     }

01672: 

01673:     /**

01674:      * Generates a valid HTML ID based the name.

01675:      * @return string the ID generated based on name.

01676:      */


Stack Trace


#0 C:\xampp\htdocs\YiiTutorial\framework\db\CDbCommand.php(272): CDbCommand->queryInternal('fetchAll', 2, Array)

#1 C:\xampp\htdocs\YiiTutorial\framework\db\ar\CActiveFinder.php(683): CDbCommand->queryAll()

#2 C:\xampp\htdocs\YiiTutorial\framework\db\ar\CActiveFinder.php(417): CJoinElement->runQuery(Object(CJoinQuery))

#3 C:\xampp\htdocs\YiiTutorial\framework\db\ar\CActiveFinder.php(142): CJoinElement->lazyFind(Object(User))

#4 C:\xampp\htdocs\YiiTutorial\framework\db\ar\CActiveRecord.php(240): CActiveFinder->lazyFind(Object(User))

#5 C:\xampp\htdocs\YiiTutorial\framework\db\ar\CActiveRecord.php(108): CActiveRecord->getRelated('community')

#6 C:\xampp\htdocs\YiiTutorial\framework\web\helpers\CHtml.php(1664): CActiveRecord->__get('community')

#7 C:\xampp\htdocs\YiiTutorial\framework\zii\widgets\CDetailView.php(201): CHtml::value(Object(User), 'community.id')

#8 C:\xampp\htdocs\YiiTutorial\framework\web\CBaseController.php(166): CDetailView->run()

#9 C:\xampp\htdocs\BuilderApp\public_html\protected\views\user\view.php(29): CBaseController->widget('zii.widgets.CDe...', Array)

#10 C:\xampp\htdocs\YiiTutorial\framework\web\CBaseController.php(119): require('C:\xampp\htdocs...')

#11 C:\xampp\htdocs\YiiTutorial\framework\web\CBaseController.php(88): CBaseController->renderInternal('C:\xampp\htdocs...', Array, true)

#12 C:\xampp\htdocs\YiiTutorial\framework\web\CController.php(798): CBaseController->renderFile('C:\xampp\htdocs...', Array, true)

#13 C:\xampp\htdocs\YiiTutorial\framework\web\CController.php(739): CController->renderPartial('view', Array, true)

#14 C:\xampp\htdocs\BuilderApp\public_html\protected\controllers\UserController.php(55): CController->render('view', Array)

#15 [internal function]: UserController->actionView('4')

#16 C:\xampp\htdocs\YiiTutorial\framework\web\actions\CInlineAction.php(47): ReflectionMethod->invokeArgs(Object(UserController), Array)

#17 C:\xampp\htdocs\YiiTutorial\framework\web\CController.php(300): CInlineAction->run()

#18 C:\xampp\htdocs\YiiTutorial\framework\web\filters\CFilterChain.php(133): CController->runAction(Object(CInlineAction))

#19 C:\xampp\htdocs\YiiTutorial\framework\web\filters\CFilter.php(41): CFilterChain->run()

#20 C:\xampp\htdocs\YiiTutorial\framework\web\CController.php(1049): CFilter->filter(Object(CFilterChain))

#21 C:\xampp\htdocs\YiiTutorial\framework\web\filters\CInlineFilter.php(59): CController->filterAccessControl(Object(CFilterChain))

#22 C:\xampp\htdocs\YiiTutorial\framework\web\filters\CFilterChain.php(130): CInlineFilter->filter(Object(CFilterChain))

#23 C:\xampp\htdocs\YiiTutorial\framework\web\CController.php(283): CFilterChain->run()

#24 C:\xampp\htdocs\YiiTutorial\framework\web\CController.php(257): CController->runActionWithFilters(Object(CInlineAction), Array)

#25 C:\xampp\htdocs\YiiTutorial\framework\web\CWebApplication.php(324): CController->run('view')

#26 C:\xampp\htdocs\YiiTutorial\framework\web\CWebApplication.php(121): CWebApplication->runController('user/view')

#27 C:\xampp\htdocs\YiiTutorial\framework\base\CApplication.php(135): CWebApplication->processRequest()

#28 C:\xampp\htdocs\BuilderApp\public_html\index.php(13): CApplication->run()

#29 {main}




then further down it shows


15:09:21.939163 	error 	system.db.CDbCommand 	


Error in querying SQL: SELECT `community`.`id` AS `t1_c0`,

`community`.`locationID` AS `t1_c1`, `community`.`name` AS `t1_c2`,

`community`.`description` AS `t1_c3`, `community`.`features` AS `t1_c4`,

`community`.`directions` AS `t1_c5`, `community`.`phone` AS `t1_c6`,

`community`.`salesPerson` AS `t1_c7`, `community`.`email` AS `t1_c8`,

`community`.`logoURL` AS `t1_c9`, `community`.`monumentURL` AS `t1_c10`,

`community`.`imageURL1` AS `t1_c11`, `community`.`imageURL2` AS `t1_c12`,

`community`.`imageURL3` AS `t1_c13`, `community`.`platURL` AS `t1_c14`,

`community`.`mapURL` AS `t1_c15`, `community`.`deleted` AS `t1_c16`,

`community`.`active` AS `t1_c17` FROM `community` `community`  WHERE

(`community`.`id'`=:ypl0)

in C:\xampp\htdocs\YiiTutorial\framework\zii\widgets\CDetailView.php (201)

in C:\xampp\htdocs\BuilderApp\public_html\protected\views\user\view.php

(29)

in

C:\xampp\htdocs\BuilderApp\public_html\protected\controllers\UserController.php

(55)

By the way, I wanted to mention that I appreciate all of your help. :)

Sorry, I’m out of suggestions for the moment. I get a lazy loaded second query when I add a related field and it works flawlessly (same result as you got using a single related field). If I find out something more I will let you know (perhaps I will add a second related table).

BTW the t1_c1 style of aliases is still there in 1.1.4.

/Tommy

Well, thanks for your help anyway. I really appreciate it.

Maybe someone else will see something we’ve overlooked.

Just want to confirm that a second BELONGS_TO relation works fine (a third query is generated for this relation, but in your case probably the second one is the one that fails, due to field order).

Other differences:

I use Linux, you use Windows

I don’t use the word ‘id’ for PK.

Just some thoughts.

/Tommy

Yes, it does work. If I place ‘userLevel.description’, before ‘community.id’, it still gives the same error. Plus, if I remove ‘community.id’, completely, it works just fine.

I finally figured out the problem!!

The FK comment for the communityID column in user table was missing the parenthesis at the end.


CONSTRAINT FOREIGN KEY (communityID) REFERENCES community(id

With your help, I was able to eliminate the framework as the cause of the error and went looking elsewhere, and the only other place to look was the database.

I actually found the same problem in another table but just hadn’t started working with that table yet to notice the error.

Thanks again for all your help. :D