Hello, I am trying to make criteria that joins with another table. They both have the same attr. “clock_num”, but when I refer to the first table it says it doesn’t know what table/column I’m talking about:
$criteria=new CDbCriteria;
$criteria->join="INNER JOIN ProjectPermissions ON ProjectPermissions.clock_num='" . Yii::app()->user->id . "'";
$criteria->compare('Projects.clock_num','whatever'); //Problem line - if I omit "Projects." then it says its an ambiguous column, obviously - so how do I refer to this attr from the left table? thanks
$dataProvider=new CActiveDataProvider('Projects', array(
'criteria'=>$criteria,
));
$this->widget('zii.widgets.CListView', array(
'dataProvider'=>$dataProvider,
'itemView'=>'_view'));
Thanks, that worked. But now in my _view.php page I am trying to access a column value from the second table, ProjectPermissions, but it says Projects.x is not defined. I would have thought that an INNER JOIN would have included columns from both tables, but I’m not sure how to refer to the columns from the second. Any help is appreciated - thanks.
echo $data->name; //works, since name is part of Projects
echo $data->access; //doesn't, since access is part of ProjectPermissions
What I mean is, the query gets the appropriate rows, but I just want the value of one column from the second table - how do I refer to that column without getting an error?
/* criteria */
$criteria=new CDbCriteria;
$criteria->select = "t.name, ProjectPermissions.access"; //I want to get this access column value in the _view.php page
$criteria->condition = "t.clock_num != '" . Yii::app()->user->id . "'";
$criteria->join = "INNER JOIN ProjectPermissions ON ProjectPermissions.clock_num='" . Yii::app()->user->id . "' AND ProjectPermissions.pid=t.id";
/* dataProvider */
$dataProvider=new CActiveDataProvider('Projects', array(
'criteria'=>$criteria,
));
/* call listview */
$this->widget('zii.widgets.CListView', array(
'dataProvider'=>$dataProvider,
'itemView'=>'_view'));
/* _view.php */
echo $data->name; //works fine
echo $data->access; //doesn't work, 'cannot find Projects.access'
Actually when I print_r the $data array send to _view, it doesn’t even have the values for the access column even though I put it in the select attribute. It seems like you can’t get the values from the other table this way…
Did you read the link I sent you? Where is your criteria and dataProvider. In search method?
In your model:
If you have relations check their names. Then do like this:
//if relation is one-to-many (because its an array when its one-to-many):
echo @$data->relation_name_for_project_permissions_table[0]->access; //its going to take the first one.
//if relation is one-to-one (then its just an object):
echo @$data->relation_name_for_project_permissions_table->access;
//i put @ to not get any errors when the object doesn't exist. It is just a php thing. you know that.
If you have further questions i’ll post some code for you but please read the relational AR link that I sent you because It explains how the relations work and how to access them and basically everything about them.
