Undestanding Relational Queries with CActiveDataProvider

Hi,

I’m having trouble figuring out how to run a query where the criteria is in a related table.

I’ve got two tables in a many to many relationship

and my controller is

public function actionAdmin()

{


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


                    'criteria'=>array(


                            'with'=>array('table2'),


                            'condition'=>'id=:table2.table1_id AND User_id =' .Yii::app()->user->id,


                            'pagination'=>array(


			'pageSize'=>self::PAGE_SIZE,


		),


	));


    //Set new property


    //$dataProvider->joinAll = true;


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


		'dataProvider'=>$dataProvider,


	));


}

I got this error

CDbCommand failed to execute the SQL statement: SQLSTATE[HY093]: Invalid parameter number: no parameters were bound

Do you have any idea where is the problem




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

    'criteria'=>array(

        'with'=>array('table2'),

        'condition'=>'id=:table2.table1_id AND User_id =' .Yii::app()->user->id,

     ),

     'pagination'=>array(

        'pageSize'=>self::PAGE_SIZE,

    ),

));



Here is my sql query

select t1.se,t2.ve,t1.ma,t3.d,t1.wa from CRecords t1,Firm t2, CPhones t3 where t1.id=t3.CRecords_id and t1.Firm_id=t2.id;

Can you help me the controller action

Not sure what you’re asking? I was just pointing out an error in how you built the array for CActiveDataProvider’s second parameter. Did it help?

Edit: I should have quoted the entire function:


public function actionAdmin()

{

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

        'criteria'=>array(

            'with'=>array('table2'),

            'condition'=>'id=:table2.table1_id AND User_id =' .Yii::app()->user->id,

        ),

        'pagination'=>array(

            'pageSize'=>self::PAGE_SIZE,

        ),

    ));

    //Set new property

    //$dataProvider->joinAll = true;

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

        'dataProvider'=>$dataProvider,

    ));

}



Condition is not working. I got this error

CDbException

Description

CDbCommand failed to execute the SQL statement: SQLSTATE[HY093]: Invalid parameter number: no parameters were bound

Source File

yii/framework/db/CDbCommand.php(375)

00363: }

00364:

00365: if($this->_connection->enableProfiling)

00366: Yii::endProfile(‘system.db.CDbCommand.query(’.$this->getText().’)’,‘system.db.CDbCommand.query’);

00367:

00368: return $result;

00369: }

00370: catch(Exception $e)

00371: {

00372: if($this->_connection->enableProfiling)

00373: Yii::endProfile(‘system.db.CDbCommand.query(’.$this->getText().’)’,‘system.db.CDbCommand.query’);

00374: Yii::log('Error in querying SQL: '.$this->getText().$par,CLogger::LEVEL_ERROR,‘system.db.CDbCommand’);

00375: throw new CDbException(Yii::t(‘yii’,‘CDbCommand failed to execute the SQL statement: {error}’,

00376: array(’{error}’=>$e->getMessage())));

00377: }

00378: }

00379: }

From a quick search this error seems often to be tied to single quote marks or question marks ("’" or “?”) somewhere in the data. Could that be it?




id=:table2.table1_id



Declares a parameter placeholder (and "table2.table1_id" may be an invalid placeholder identifier)

Try




id=table2.table1_id



(not tested)

/Tommy

I tried and got this error

CDbCommand failed to execute the SQL statement: SQLSTATE[23000]: Integrity constraint violation: 1052 Column ‘id’ in where clause is ambiguous

This is my action controller

public function actionAdmin()

{


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


	'criteria'=>array(


	'with'=>array('Phones'),


	'condition'=>'id=Phones.Records_id AND User_id =' .Yii::app()->user->id,


    ),   	


		'pagination'=>array(


			'pageSize'=>self::PAGE_SIZE,


		),


	));





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


		'dataProvider'=>$dataProvider,


	));


}

Try this




'condition'=>'t.id=Phones.Records_id AND User_id =' .Yii::app()->user->id,



/Tommy

Still no luck

CDbException

Description

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

Source File

I have the same problem when I want to get category and section name for each catid and section id on content table.

This is my codes:

ContentController:




public function actionAdmin()

{

	/*$criteria=new CDbCriteria(array(

		'with'=>'categories',

			 'condition'=>'catid=:categories.id'

	));*/

		

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

		'criteria'=>array(

			'with'=>array('categories','sections'),

			 	'condition'=>'categories.id=:content.catid',

			 //	'params'=>array(':content.catid'=>'content.catid')

		),

		'pagination'=>array(

			'pageSize'=>self::PAGE_SIZE,

		),

			

	));


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

			'dataProvider'=>$dataProvider,

	));

}



/models/content.php




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(

		'categories' => array(self::HAS_ONE, 'categories', 'id'),

		'sections' => array(self::BELONGS_TO, 'sections', 'id'));

}



aplication log




2010/03/12 02:36:20 [trace] [system.web.CModule] Loading "log" application component

2010/03/12 02:36:20 [trace] [system.web.CModule] Loading "request" application component

2010/03/12 02:36:20 [trace] [system.web.CModule] Loading "urlManager" application component

2010/03/12 02:36:20 [trace] [system.web.filters.CFilterChain] Running filter ContentController.filteraccessControl()

2010/03/12 02:36:20 [trace] [system.web.CModule] Loading "user" application component

2010/03/12 02:36:20 [trace] [system.web.CModule] Loading "session" application component

2010/03/12 02:36:20 [trace] [system.web.CModule] Loading "db" application component

2010/03/12 02:36:20 [trace] [system.db.CDbConnection] Opening DB connection

2010/03/12 02:36:20 [trace] [system.db.CDbCommand] Querying SQL: SHOW COLUMNS FROM `content`

2010/03/12 02:36:20 [trace] [system.db.CDbCommand] Querying SQL: SHOW CREATE TABLE `content`

2010/03/12 02:36:20 [trace] [system.db.CDbCommand] Querying SQL: SHOW COLUMNS FROM `categories`

2010/03/12 02:36:20 [trace] [system.db.CDbCommand] Querying SQL: SHOW CREATE TABLE `categories`

2010/03/12 02:36:20 [trace] [system.db.CDbCommand] Querying SQL: SHOW COLUMNS FROM `sections`

2010/03/12 02:36:20 [trace] [system.db.CDbCommand] Querying SQL: SHOW CREATE TABLE `sections`

2010/03/12 02:36:20 [trace] [system.db.ar.CActiveRecord] content.count() eagerly

2010/03/12 02:36:20 [trace] [system.db.CDbCommand] Querying SQL: SELECT COUNT(DISTINCT `t`.`id`) FROM `content` `t`  LEFT OUTER JOIN `categories` `categories` ON (`categories`.`id`=`t`.`id`) LEFT OUTER JOIN `sections` `sections` ON (`t`.`id`=`sections`.`id`) WHERE (categories.id=:content.catid)

2010/03/12 02:36:20 [error] [system.db.CDbCommand] Error in querying SQL: SELECT COUNT(DISTINCT `t`.`id`) FROM `content` `t`  LEFT OUTER JOIN `categories` `categories` ON (`categories`.`id`=`t`.`id`) LEFT OUTER JOIN `sections` `sections` ON (`t`.`id`=`sections`.`id`) WHERE (categories.id=:content.catid)

2010/03/12 02:36:20 [trace] [system.web.CModule] Loading "coreMessages" application component

2010/03/12 02:36:20 [trace] [system.web.CModule] Loading "errorHandler" application component



Error:




CDbCommand failed to execute the SQL statement: SQLSTATE[HY093]: Invalid parameter number: no parameters were bound



Yeah, how to understanding relational queries with CActiveDataProvider?

Solved!

In /model/content.php should be like this:




public function relations()

{

	return array(

		'categories' => array(self::BELONGS_TO , 'categories', 'catid'),

		'sections' => array(self::BELONGS_TO, 'sections', 'sectionid'));

}



Or is there any better way?

I heve the same problem.

My error is :


CDbCommand failed to execute the SQL statement: SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'user_id' in on clause is ambiguous. The SQL statement executed was: SELECT COUNT(DISTINCT `t`.`id`) FROM `project` `t` LEFT OUTER JOIN `user_project` `users_users` ON (`t`.`id`=`users_users`.`project_id`) LEFT OUTER JOIN `user` `users` ON (`users`.`id`=`users_users`.`user_id`) LEFT OUTER JOIN `user` `author` ON (`t`.`author_id`=`author`.`id`) LEFT OUTER JOIN `task` `tasks` ON (`tasks`.`project_id`=`t`.`id`) AND (user_id='49') 

User_id is not exist in "project" , but exist in "task" , well why a "user_id" is ambiguous?

My code in project Controller:


public function defaultScope() {

        if (Yii::app()->user->checkAccess('administrator')) {

            return array();

        } else {

            return array(

                      'condition' => "`user_id`='" . Yii::app()->user->id . "'",

            );

        }

    }

INFO: I render task table in Project View.

Relation Project:


 return array(

            'tasks' => array(self::HAS_MANY, 'Task', 'project_id'),

            'users' => array(self::MANY_MANY, 'User', 'user_project(project_id, user_id)'),

            'author' => array(self::BELONGS_TO, 'User', 'author_id'),

        );

    }

relation task:


return array(

            'messages' => array(self::HAS_MANY, 'Message', 'task_id'),

            'project' => array(self::BELONGS_TO, 'Project', 'project_id'),

            'user' => array(self::BELONGS_TO, 'User', 'user_id'),

            'author' => array(self::BELONGS_TO, 'User', 'author_id'),

                //    'file' => array(self::BELONGS_TO, 'Files', 'path'),

        );

    }

Where is problem?