[ps_sach]

Hi all,
I am trying to perform relational query to the database and in where clause I have one ambiguous column, so I am using table.ambiguouscolumn_name in where clause, but its giving me error like table.ambiguouscolumn_name is not defined.

For example

 $usermodel = new user;
 $criteria = new CDbCriteria;
 $criteria->select = array("UserId", "FullName","CreatedDate","Status");
 $criteria->condition = "user.CustomerId=:CustomerId";
 $parameters[":CustomerId"] = $this->customerid;
 $pages=new CPagination($usermodel->count($criteria));

Exception I am getting is -

Quote

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

I went through the debug logs, its generating query like -

Quote

Querying SQL: SELECT COUNT(*) FROM `user` `t` WHERE
user.CustomerId=:CustomerId. Bind with parameter :CustomerId='1'

Query says its creating alias for the table as t but in my where clause its user.CustomerId, so its giving exception.

I hope there must be some simple solution to this.

Thanks!

[amc]

ps_sach, on 01 February 2010 - 06:05 AM, said:

Hi all,
I am trying to perform relational query to the database and in where clause I have one ambiguous column, so I am using table.ambiguouscolumn_name in where clause, but its giving me error like table.ambiguouscolumn_name is not defined.

For example

 $usermodel = new user;
 $criteria = new CDbCriteria;
 $criteria->select = array("UserId", "FullName","CreatedDate","Status");
 $criteria->condition = "user.CustomerId=:CustomerId";
 $parameters[":CustomerId"] = $this->customerid;
 $pages=new CPagination($usermodel->count($criteria));

Exception I am getting is -




I went through the debug logs, its generating query like -



Query says its creating alias for the table as t but in my where clause its user.CustomerId, so its giving exception.

I hope there must be some simple solution to this.

Thanks!

Since version 1.1 Yii now uses a default alias of 't' for the domain class in active record queries.

You need to change your query criteria as follows

 $criteria->condition = "t.CustomerId=:CustomerId";

or alternatively

 $criteria->condition = "CustomerId=:CustomerId";

if the column name is unique within the query

amc

[ps_sach]

amc, on 02 February 2010 - 03:35 PM, said:

Since version 1.1 Yii now uses a default alias of 't' for the domain class in active record queries.

You need to change your query criteria as follows

 $criteria->condition = "t.CustomerId=:CustomerId";

or alternatively

 $criteria->condition = "CustomerId=:CustomerId";

if the column name is unique within the query

amc

Thank you for the reply amc!
I don't have unique column name in the query, so according to u I have to use -

 $criteria->condition = "t.CustomerId=:CustomerId";

I have user table and group table, both having common CustomerId field. I want to retrive some data from group table to which user is associated with and user must belongs to particular CustomerId.
If the query is like

Quote

$query = 'Select username,groupname from user,group where user.CustomerId = 1;

How Yii will interpret this query? Is it going to create alias t for both or t1 for user and t2 for group? And what should I use in where clause.
I think its bit confusing to blindly use alias in where clause and suppose in next version yii changes this alias concept to something else?

[ps_sach]

I migrated to 1.1 from 1.0.11 and getting error in relational queries. My sample code is -

                        $UserModel = new User;
			$criteria = new CDbCriteria;
				
			$criteria->select = array("UserId", "FullName","CreatedDate","Status");
			$criteria->condition = "user.CustomerId=:CustomerId";
			$parameters[":CustomerId"] = $this->customerId;
                        if(isset($_POST['User']))
			{ 
                           $UserModel->attributes = $_POST['User'];

			   if($UserModel->FullName != '')
			   {
				  $criteria->condition .= " AND FullName LIKE :FullName";
				  parameters[":FullName"] = "%".$UserModel->FullName."%";
			  }
	
			  if($UserModel->LocationId != '')
			  {
				  $criteria->condition .= " AND user.LocationId=:LocationId";
				  $parameters[":LocationId"] = $UserModel->LocationId;
			  }
									
			  if($UserModel->Status != '')
			  {
				  $criteria->condition .= " AND Status=:Status";
				  $parameters[":Status"] = $UserModel->Status;
			   }
				
                         }
			 $criteria->params = $parameters;
			
			  $pages = new CPagination(User::model()->with(array("location" => array('select' => 'LocationName')))->count($criteria));

Exception I got is -

Quote

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

query in the debug log is -

Quote

Error in querying SQL: SELECT COUNT(DISTINCT `t`.`UserId`) FROM `user`
`t` LEFT OUTER JOIN `location` `location` ON
(`t`.`LocationId`=`location`.`LocationId`) WHERE
(user.CustomerId=:CustomerId). Bind with parameter :CustomerId='1'

The solution to this is use t instead of user ( tablename ) in where clause. I think this shouldn't be only solution to this problem. It is very bad to go to each and every page and change the where clause?

Anybody please help!

[Horacio Segura]

look this
http://code.google.c...s/detail?id=796
http://code.google.c...s/detail?id=817
http://code.google.c...s/detail?id=818

Quote

$query = 'Select username,groupname from user,group where user.CustomerId = 1;

look http://www.yiiframew...ng-column-names
this select with AR is like

User::model()->with('group')->find($criteria);

$criteria->condition='t.CustomerId = 1';
or
$criteria->condition='group.CustomerId = 1';

the alias for relations is the name for relation(i supose group )
http://www.yiiframew...l-query-options (alias option)

I hope help you

[ps_sach]

Thanks a lot Horacio Segura for your reply!
I got how table aliases work in yii and I need to change my code to get it working with 1.1.



Thanks again!