Yii Framework Forum: Undestanding Relational Queries with CActiveDataProvider - Yii Framework Forum

Jump to content

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

Undestanding Relational Queries with CActiveDataProvider Rate Topic: -----

#1 User is offline   nemo 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 142
  • Joined: 24-February 10

Posted 10 March 2010 - 08:40 AM

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
0

#2 User is offline   jsoo 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 65
  • Joined: 01-March 10
  • Location:Durham, NC, USA

Posted 10 March 2010 - 08:49 AM

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

0

#3 User is offline   nemo 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 142
  • Joined: 24-February 10

Posted 10 March 2010 - 09:36 AM

View Postjsoo, 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
0

#4 User is offline   jsoo 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 65
  • Joined: 01-March 10
  • Location:Durham, NC, USA

Posted 10 March 2010 - 10:06 AM

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

0

#5 User is offline   nemo 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 142
  • Joined: 24-February 10

Posted 10 March 2010 - 10:29 AM

View Postjsoo, 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: }
0

#6 User is offline   jsoo 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 65
  • Joined: 01-March 10
  • Location:Durham, NC, USA

Posted 10 March 2010 - 11:15 AM

View Postnemo, 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?
0

#7 User is offline   tri 

  • Elite Member
  • Yii
  • Group: Moderators
  • Posts: 1,651
  • Joined: 20-November 08
  • Location:Stockholm, Sweden

Posted 10 March 2010 - 11:46 AM

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

Don't forget to read The Definitive Guide to Yii (en) (sv) | The class reference has the details
0

#8 User is offline   nemo 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 142
  • Joined: 24-February 10

Posted 10 March 2010 - 03:18 PM

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

#9 User is offline   tri 

  • Elite Member
  • Yii
  • Group: Moderators
  • Posts: 1,651
  • Joined: 20-November 08
  • Location:Stockholm, Sweden

Posted 10 March 2010 - 03:39 PM

View Postnemo, 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
Don't forget to read The Definitive Guide to Yii (en) (sv) | The class reference has the details
0

#10 User is offline   nemo 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 142
  • Joined: 24-February 10

Posted 10 March 2010 - 04:25 PM

View Posttri, 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
0

#11 User is offline   arsitek 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 127
  • Joined: 10-October 08

Posted 11 March 2010 - 02:46 PM

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?
0

#12 User is offline   arsitek 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 127
  • Joined: 10-October 08

Posted 11 March 2010 - 03:28 PM

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?
0

#13 User is offline   kopecldz 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 86
  • Joined: 20-February 13

Posted 10 April 2013 - 05:10 AM

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