Yii Framework Forum: Trouble Searching Through Relations - Yii Framework Forum

Jump to content

Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

Trouble Searching Through Relations Rate Topic: -----

#1 User is offline   bachem 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 50
  • Joined: 17-January 12
  • Location:Costa Rica

Posted 07 February 2013 - 05:00 PM

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


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...
0

#2 User is offline   redguy 

  • Master Member
  • PipPipPipPip
  • Yii
  • Group: Members
  • Posts: 711
  • Joined: 02-July 10
  • Location:Central Poland

Posted 08 February 2013 - 04:06 AM

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

#3 User is offline   bachem 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 50
  • Joined: 17-January 12
  • Location:Costa Rica

Posted 08 February 2013 - 10:52 AM

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)

0

#4 User is offline   seenivasan 

  • Master Member
  • PipPipPipPip
  • Yii
  • Group: Members
  • Posts: 620
  • Joined: 17-June 12
  • Location:Chennai,TamilNadu,India.

Posted 08 February 2013 - 11:13 AM

Dear Friend

Let us see what happens when we add the following.

$criteria->together=true;

0

#5 User is offline   bachem 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 50
  • Joined: 17-January 12
  • Location:Costa Rica

Posted 08 February 2013 - 12:04 PM

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


0

#6 User is offline   le_top 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 252
  • Joined: 08-June 10
  • Location:France

Posted 09 February 2013 - 04:37 AM

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.
0

#7 User is offline   le_top 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 252
  • Joined: 08-June 10
  • Location:France

Posted 09 February 2013 - 04:48 AM

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.
0

#8 User is offline   softark 

  • Keep It Simple
  • Yii
  • Group: Moderators
  • Posts: 2,005
  • Joined: 16-February 11
  • Location:Japan

Posted 09 February 2013 - 05:00 AM

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

0

#9 User is offline   bachem 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 50
  • Joined: 17-January 12
  • Location:Costa Rica

Posted 11 February 2013 - 10:45 AM

View Postsoftark, 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
0

#10 User is offline   bachem 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 50
  • Joined: 17-January 12
  • Location:Costa Rica

Posted 11 February 2013 - 11:02 AM

View Postle_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?
0

#11 User is offline   le_top 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 252
  • Joined: 08-June 10
  • Location:France

Posted 11 February 2013 - 11:20 AM

Hi

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

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

#12 User is offline   softark 

  • Keep It Simple
  • Yii
  • Group: Moderators
  • Posts: 2,005
  • Joined: 16-February 11
  • Location:Japan

Posted 11 February 2013 - 11:22 AM

View Postbachem, 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));

0

#13 User is offline   bachem 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 50
  • Joined: 17-January 12
  • Location:Costa Rica

Posted 11 February 2013 - 12:05 PM

View Postsoftark, 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
0

#14 User is offline   le_top 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 252
  • Joined: 08-June 10
  • Location:France

Posted 11 February 2013 - 12:10 PM

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.
0

#15 User is offline   yJeroen 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 94
  • Joined: 06-September 11
  • Location:The Netherlands

Posted 11 February 2013 - 12:19 PM

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 ?
0

#16 User is offline   bachem 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 50
  • Joined: 17-January 12
  • Location:Costa Rica

Posted 11 February 2013 - 12:58 PM

View PostyJeroen, 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.
0

#17 User is offline   yJeroen 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 94
  • Joined: 06-September 11
  • Location:The Netherlands

Posted 11 February 2013 - 01:11 PM

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;
0

#18 User is offline   le_top 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 252
  • Joined: 08-June 10
  • Location:France

Posted 11 February 2013 - 02:43 PM

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

0

#19 User is offline   FlyngThunderGod 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 5
  • Joined: 29-January 13

Posted 11 February 2013 - 06:12 PM

View Postbachem, 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.
0

#20 User is offline   softark 

  • Keep It Simple
  • Yii
  • Group: Moderators
  • Posts: 2,005
  • Joined: 16-February 11
  • Location:Japan

Posted 12 February 2013 - 03:26 AM

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.
1

Share this topic:


Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

1 User(s) are reading this topic
0 members, 1 guests, 0 anonymous users