slyder
June 17, 2009, 2:28pm
1
Hello, i have 2 table. For example users and posts.
Both tables have fields with same name (lang).
I have next DB criteria:
$criteria = new CDbCriteria;
$withOption = array('user' => array(
'condition' => '`lang` = :lang',
'params' => array('lang' => $lang)
));
the problem is what for current where clause column is ambiguous, but if i add table name:
$withOption = array('user' => array(
'condition' => 'users.`lang` = :lang',
'params' => array('lang' => $lang)
));
i have error what column users.lang doesn't exist, becuase Yii adds alias to users table.
How to define table name for such fields?
Thanks!
qiang
June 17, 2009, 2:48pm
2
How do you use $withOption?
slyder
June 17, 2009, 2:50pm
3
$posts = Posts::model()->with($withOption)->findAll($criteria);
See "Disambiguating Column Names" in: http://www.yiiframework.com/doc/guide/database.arr
If you use 1.0.x watch out for the note:
"Note: the behavior of column disambiguation has been changed since version 1.1.0. Previously in version 1.0.x, by default Yii would automatically generate a table alias for each relational table, and we had to use the prefix ??. to refer to this automatically generated alias."
So in 1.0.x you should use "??." to disambiguate columns.
iGrog
December 15, 2009, 3:26pm
6
Ok, I don’t understand.
I have tables:
Users (UID [PK, autoinc], …)
Photos (PID [PK, autoinc], UID [FK], …)
Ratings (RID [PK, autoinc], PID [FK], UID [FK], Rating)
Each photo my have only one Rating from one User.
So, what do I need to write in Photos model and in Ratings model and in Users model?
Next, I’m trying to get photo info from Photos table and, if exists, user’s rating for this photo in the same query.
$criteria = new CDbCriteria;
$criteria->condition = "PID=:pid AND UID=:uid";
$criteria->params = array(":uid" => $uid, ":pid" => $pid);
$model = Photos::model()->with("ratings")->together()->find($criteria);
if($model->ratings != null) { ... }
iGrog:
Ok, I don’t understand.
I have tables:
Users (UID [PK, autoinc], …)
Photos (PID [PK, autoinc], UID [FK], …)
Ratings (RID [PK, autoinc], PID [FK], UID [FK], Rating)
Each photo my have only one Rating from one User.
So, what do I need to write in Photos model and in Ratings model and in Users model?
Next, I’m trying to get photo info from Photos table and, if exists, user’s rating for this photo in the same query.
$criteria = new CDbCriteria;
$criteria->condition = "PID=:pid AND UID=:uid";
$criteria->params = array(":uid" => $uid, ":pid" => $pid);
$model = Photos::model()->with("ratings")->together()->find($criteria);
if($model->ratings != null) { ... }
For example:
Users.php
class Users extends CActiveRecord
{
...
public function relations()
{
return array(
'photos' => array(self::HAS_MANY, 'Photos', 'UID'),
'ratings' => array(self::HAS_MANY, 'Ratings', 'UID'),
);
}
...
}
Photos.php
class Photos extends CActiveRecord
{
...
public function relations()
{
return array(
'user' => array(self::BELONGS_TO, 'Users', 'UID'),
'ratings' => array(self::HAS_MANY, 'Ratings', 'PID'),
'user_rating' => array(self::HAS_ONE, 'Ratings', 'PID', 'condition' => '??.UID=:uid'),
);
}
...
}
Ratings.php
class Ratings extends CActiveRecord
{
...
public function relations()
{
return array(
'user' => array(self::BELONGS_TO, 'Users', 'UID'),
'photo' => array(self::BELONGS_TO, 'Photos', 'PID'),
);
}
...
}
Get a given user’s rating on a photo:
$model = Photos::model()->with(
array(
"user_rating" => array(
'params' => array(':uid' => the id of the user)
)
)
)
->find($criteria);
You meant something like this?
