[nemo]

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

[jsoo]

$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,
    ),
));

[nemo]

jsoo, on 10 March 2010 - 08:49 AM, said:

		$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

[jsoo]

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,
    ));
}

[nemo]

jsoo, on 10 March 2010 - 10:06 AM, said:

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: }

[jsoo]

nemo, on 10 March 2010 - 10:29 AM, said:

SQLSTATE[HY093]: Invalid parameter number: no parameters were bound

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?

[tri]

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

This post has been edited by tri: 10 March 2010 - 12:00 PM

[nemo]

tri, on 10 March 2010 - 11:46 AM, said:

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,
));
}

[tri]

nemo, on 10 March 2010 - 03:18 PM, said:

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
...

Try this

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

/Tommy

[nemo]

tri, on 10 March 2010 - 03:39 PM, said:

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

[arsitek]

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?

[arsitek]

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?

[kopecldz]

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?