Problem in relational sql queries with ambiguous column in where clause

Yii 1.1 General Discussion
1

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!

2

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

3

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

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?

4

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 -

query in the debug log is -

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!

5

look this

http://code.google.com/p/yii/issues/detail?id=796

http://code.google.com/p/yii/issues/detail?id=817

http://code.google.com/p/yii/issues/detail?id=818

look http://www.yiiframework.com/doc/guide/database.arr#disambiguating-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.yiiframework.com/doc/guide/database.arr#disambiguating-column-names#relational-query-options (alias option)

I hope help you

6

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!

7

Sorry to revive an old thread but I just wanted to post this in case someone is having the same problem as me (related to the thread, of course).

This is the situation: I have CGridView showing columns from another table and everything worked fine (I followed this tutorial: http://www.yiiframework.com/forum/index.php/topic/17595-column-disambiguation-on-named-scopes/), except that when I use the filter row and enter a value for column "name" of the original table, I got an SQL error about ambiguity (had to disambiguate the columns) because there was a column "name" in the 2nd related table.

So, to fix this, I went to my original table’s model file (/protected/models/OriginalTable.php) and modified the criteria within the search() function, like so:

From:


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

To:


$criteria->compare('t.name',$this->name,true); #notice the added "t." before the column name

This fixed the ambiguity issue. I hope it helps the next guy or girl :)