[bachem]

 Screenshot from 2013-02-07 15:10:40.png (23.76K)
Number of downloads: 14


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,
		));
	}

or

      array_push($criteria->with,'media');		
      $criteria->addSearchCondition('LOWER(media.person.first_name)', strtolower($this->person_id));

But everything I've tried fail

i will appreciate your help. best regards...

[redguy]

what exactly do you mean by 'fail'? is there some error? if so - please check application.log and provide here those logs.

[bachem]

Thanks Red

when try

        array_push($criteria->with,'media');		
	$criteria->addSearchCondition('LOWER(media.person.first_name)', strtolower($this->media_id));

Obtain CDbException

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)

[seenivasan]

Dear Friend

Let us see what happens when we add the following.

$criteria->together=true;

[bachem]

 seenivasan, on 08 February 2013 - 11:13 AM, said:

Dear Friend

Let us see what happens when we add the following.

$criteria->together=true;

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'),
                );
	}

[le_top]

Hi

I suggest that you try my extension Related Search Behavior . It makes searches through related tables easier.

It does not implement the conversion to lowercase, but I do not think that that conversion is necessary.

[le_top]

Regarding the error you get with

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.

[softark]

I think you have to set not only "media.person" but also "media" to "with".
And, you should refer to the columns of "media.person" with an alias of "person", not "media.person".

$criteria->with = array('media', 'media.person');
$criteria->addSearchCondition('LOWER(person.first_name)', strtolower($this->person_id));

[bachem]

 softark, on 09 February 2013 - 05:00 AM, said:

I think you have to set not only "media.person" but also "media" to "with".
And, you should refer to the columns of "media.person" with an alias of "person", not "media.person".

$criteria->with = array('media', 'media.person');
$criteria->addSearchCondition('LOWER(person.first_name)', strtolower($this->person_id));

Hi softark, in fact i tried that but get the same error

[bachem]

 le_top, on 09 February 2013 - 04:48 AM, said:

Regarding the error you get with

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?

[le_top]

Hi

In the 'WHERE' condition a closing parenthesis is missing.

Also, as indicated before, you should write LOWER(person.first_name) .

[softark]

 bachem, on 11 February 2013 - 11:02 AM, said:

the trouble be in where condition

WHERE (LOWER(media.person.first_name) LIKE :ycp0

yii create double alias

It's because you wrote "media.person.first_name".

Just try this:

$criteria->addSearchCondition('LOWER(person.first_name)', strtolower($this->person_id));

[bachem]

 softark, on 11 February 2013 - 11:22 AM, said:

It's because you wrote "media.person.first_name".
Just try this:
...

Understood about media.person.first_name
in relation with

$criteria->addSearchCondition('LOWER(person.first_name)', strtolower($this->person_id));

That work, but it's not functional to me, because y need media with one some person, i'm not searching directly some person

person<-media->message_log
Like the E-R diagram at the begin of the post

Thanks a lot for you time softark

[le_top]

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.

[yJeroen]

It's not clear to me what exactly you're searching..

You want all MessageLogs where a person's first name is "bachem"? (for example)

Then why are you comparing first_name with person_id ?

[bachem]

 yJeroen, on 11 February 2013 - 12:19 PM, said:

It's not clear to me what exactly you're searching..

You want all MessageLogs where a person's first name is "bachem"? (for example)

Then why are you comparing first_name with person_id ?

Exactly yJeroen i want all MessageLogs where a person's first name is "bachem".

I'm using $this->person_id like a temporal value from admin view this didn't have an id in fact have a varchar value.

[yJeroen]

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;

[le_top]

Hi
I added a sample to my extension "RelatedSearchBehavior" showing how easy searches in relations can be.

The demo is online and downloadable through the extension page.

Just note that all I have to do in the view is write this:

$model=new MessageLog('search');
if(isset($_GET['MessageLog'])) {
    $model->attributes=$_GET['MessageLog'];
}
$dataProvider=$model->search();
if($dataProvider instanceof KeenActiveDataProvider) {
    /* @var $dataProvider KeenActiveDataProvider */
    $dataProvider->withKeenLoading=array('message','media','media.person');
}

$this->widget('zii.widgets.grid.CGridView',array(
        'dataProvider'=>$dataProvider,
        'filter'=>$model,
        'columns'=>array(
                'id',
                'message_txt', // same as 'message.message',
                'first_name', // same as 'media.person.first_name',
                'last_name', // same as 'media.person.last_name',
        )
));

and essentially this in the MessageLog model (+safe attributes + use the extension):

// Added RelatedSearchBehavior.[/size]
	public function behaviors() {
    	return array(
            	'relatedsearchbehavior' => array(
                    	'class'=>'RelatedSearchBehavior',
                    	'relations'=>array(
                            	'message_txt'=>'message.message',
                            	'first_name'=>'media.person.first_name',
                            	'last_name'=>'media.person.last_name',
                            	'address'=>'media.address',
                    	),
            	),
            	);

[FlyngThunderGod]

 bachem, on 08 February 2013 - 12:04 PM, said:

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'),
                );
	}

I think you have a typo in your above code, missing a quote and property name.

[softark]

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.