I need search first_name and last_name through relations of the model MessageLog
I tried things like:
public function search()
{
// Warning: Please modify the following code to remove attributes that
// should not be searched.
$criteria=new CDbCriteria;
$criteria->with=array();
$criteria->compare('id',$this->id);
$criteria->compare('message_id',$this->message_id);
//$criteria->compare('media_id',$this->media_id);
array_push($criteria->with,'media.person');
$criteria->addSearchCondition('LOWER(media.person.first_name)', strtolower($this->person_id));
return new CActiveDataProvider($this, array(
'criteria'=>$criteria,
));
}
CDbCommand failed to execute the SQL statement: SQLSTATE[3F000]: Invalid schema name: 7 ERROR: schema "media" does not exist. The SQL statement executed was: SELECT COUNT(DISTINCT "t"."id") FROM "message_log" "t" LEFT OUTER JOIN "media" "media" ON ("t"."media_id"="media"."id") LEFT OUTER JOIN "user" "person" ON ("media"."person_id"="person"."id") WHERE (LOWER(media.person.first_name) LIKE :ycp0)
Thanks seenivasan, but get the same error, i can’t understand error message because seems like relation doesn’t exist, but that’s not true
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(
'media' => array(self::BELONGS_TO, 'Media', 'media_id'),
' => array(self::BELONGS_TO, 'Message', 'message_id'),
);
}
SELECT COUNT(DISTINCT "t"."id") FROM "message_log" "t" LEFT OUTER JOIN "media" "media" ON ("t"."media_id"="media"."id") LEFT OUTER JOIN "user" "person" ON ("media"."person_id"="person"."id") WHERE (LOWER(media.person.first_name) LIKE :ycp0
I agree that it is a bit strange, but I also find it strange that your fields are quoted with double quotes (") instead of backquotes (`) but maybe that is the way your database is adressed.
I’ld take the above SQL statement and try it directly on the database (replacing :ycp0 of course) and tweak it until I’ld get the right result.
One of the things you could check is that the fact that the alias equals the name of the table is not an issue -> change the alias name to ‘medium’ (at least for your test) which will also clarify where the error is.
Make sure that the table ‘media’ exists with exactly the same spelling and that there is no typo.
Thanks le_top i did the test with the yii query and found where is the error
SELECT COUNT(DISTINCT "t"."id") FROM "message_log" "t" LEFT OUTER JOIN "media" "media" ON ("t"."media_id"="media"."id") LEFT OUTER JOIN "user" "person" ON ("media"."person_id"="person"."id")
work fine, yii uses double " because that’s the way in postgres so it’s ok, but the trouble be in where condition
WHERE (LOWER(media.person.first_name) LIKE :ycp0
yii create double alias, so the question now is:
How avoid that and use the relation to build the query?
In the SQL query, person corresponds to the alias of the table that is linked using the join condition, so refering to ‘person’ is refering to the person depending on media.
In that case, softark’s suggestion works. Post your current search() method and the error you’re getting. In case you don’t get an error, post the SQL that shows up in your weblog.
Secondly, don’t do temporary values, it messes up your code. Add a property in the MessageLog class named: public $searchFirstName;
Just for clarification of the relation and the table alias.
When we use a relation in a query, the related table will be assigned a table alias. And the default value for that alias is as the same as the relation name.
relation name : "post" => alias : "post"
relation name : "comment" => alias : "comment"
Even when it is a relation of a relation, the alias won’t be prefixed with the parent relation name.
relation name : "post.author" => alias : "author"
This default rule may create the same aliases for different relations.
relation name : "post.author" => alias : "author"
relation name : "comment.author" => alias : "author"
In this case we can manually define the alias to disambiguate them.
// main model is "Post"
$criteria->with = array(
'author', // author of the post ... use the default alias
'comments', // comments to the post ... use the default alias
'comments.author' => array( // author of the comments to the post
'alias' => 'c_author', // ... need to define the alias manually
),
);
In bachem’s case, there’s only one “person” relation. So the alias for it could be “person”. And you have to write the alias (not the relation name) in a condition.