Jump to content
Larry Ullman's Book Forums
Sign in to follow this  
Ziggi

Search By Virtual Attribute

Recommended Posts

Hi,

 

This one is nasty. I did a research an actually found no relevant solution.

 

The task is:

 

I have a 'User' model with attributes:

 

'surname' and 'firstname'

 

But on the user search form I would like to search by 'fullname', where 'fullname' is:

 

public $fullname;
public function getFullname(){
   return $this->surname . ', ' . $this->firstname;
}

 

 

Well,

 

I seems not that easy. First of all - there is no easy way to feed virtual attribute to search dataprovider. The only way is through computed column:

 

$criteria=new CDbCriteria;
$criteria->select = array('*', 'CONCAT(surname, ", ", firstname) AS fullname');
$criteria->compare('fullname', $this->fullname, true);

 

 

This is good enough to feed 'fullname' to dataprovider but actual search fails with error:

 

CDbCommand failed to execute the SQL statement: SQLSTATE[42S22]: Column not found 'fullname'

 

No big surprise actually, but the problem persists and I am just curious what is the right way to solve it.

 

If anybody can share some ideas I would appreciate.

 

Rgs,

Ziggi

Share this post


Link to post
Share on other sites

:-)

 

Yeap - that was too easy:

 

$criteria->compare('CONCAT(surname, ", ", firstname)',$this->fullname,true);

 

I can have a drink now ;-)

  • Upvote 1

Share this post


Link to post
Share on other sites

Hmm,

 

This actually works but... javascript search suggestions do not show up while typing into 'fullname' field. Looks like look-up while typing has issues with such a complex stuff.

Share this post


Link to post
Share on other sites

According to a Stack Overflow answer, the way you did it the first time should really work. The question is, did you put the method in the User Model or in the Search Model? From the looks of it, it's in the search model. Try placing it in the User model instead.

 

The only reason I see for this failing, is that the Active Record of YII looks at table definitions and compare it with public attributes set. Maybe you need to remove that attribute and let YII use the method declared instead? I don't really know if this is any problem, but give it a try if you meet troubles.

Share this post


Link to post
Share on other sites

Hi Antonio,

 

It's all about User model. There is no special 'Search' model but there are search criteria within a User model class.

 

This is my Users.php model:

 

<?php

/**
* This is the model class for table "users".
*/

class Users extends CActiveRecord
{

public $fullname;


public static function model($className=__CLASS__)
{
return parent::model($className);
}


public function tableName()
{
return 'users';
}


public function rules()
{

return array(

	 array('login, pass_hash, surname, firstname', 'filter', 'filter' => 'trim'),
	 array('login, pass_hash, surname, firstname', 'filter', 'filter' => 'strip_tags'),

	 array('id', 'numerical', 'integerOnly'=>true, 'min'=>0, 'except'=>'insert'),

	 array('login', 'required', 'on'=>'insert'),
	 array('login', 'length', 'encoding'=>'utf8', 'min'=>5, 'max'=>16),
	 array('login', 'match', 'pattern'=>'/^[a-z]{1}[a-z0-9-_]*$/'),
	 array('login', 'unique', 'caseSensitive'=>FALSE),

	 array('pass_hash', 'required', 'on'=>'insert'),
	 array('pass_hash', 'length', 'is'=>32),

	 array('surname, firstname, email, group, active', 'required'),

	 array('surname, firstname', 'length', 'encoding'=>'utf8', 'min'=>2, 'max'=>32),
	 array('surname, firstname', 'match', 'pattern'=>'/^[A-Z]{1}[a-zA-Z- ]*$/'),

	 array('email', 'length', 'max'=>64),
	 array('email', 'email', 'checkMX'=>TRUE),

	 array('group', 'in', 'range'=>array('account','designer','customer','admin')),

	 array('active', 'boolean', 'strict'=>TRUE),

	 array('fullname', 'safe', 'on'=>'search'),

);
}

public function attributeLabels()
{
return array(
'login' => 'Login',
'pass_hash' => 'Pass',
'surname' => 'Family name',
'firstname' => 'Given name',
'email' => 'E-mail',
'group' => 'Role',
'active' => 'Is active?',
'fullname' => 'Name',
);
}


public function search()
{

$criteria=new CDbCriteria;

 $criteria->select = array('*', 'CONCAT(surname, ", ", firstname) AS fullname');

 $criteria->compare('id',$this->id,true);
 $criteria->compare('login',$this->login,true);
 $criteria->compare('CONCAT(surname, ", ", firstname)',$this->fullname,true);
 $criteria->compare('group',$this->group,true);
 $criteria->compare('active',$this->active);

return new CActiveDataProvider($this, array(
'criteria'=>$criteria,
));
}
}

 

This is the only way actually working - excluding javascript 'drop down' seach suggesions on filter in case of 'fullname' one.

 

I really tried it starting from plain virtual attribute but this do not goes into search dataprovider. According my findings - the configuration I have just provided above is the only one really proven working. Virtual attribute getter is not applicable in this context.

 

So now the only problem is this misfortune search suggestion drop-down on filter and I consider this minor problem. But if you have any idea on that - I would appreciate.

Share this post


Link to post
Share on other sites

Hi,

 

You know what... human stupidity has no limits actually. My solution works perfectly. The "drop-down" issue I have mentioned was... an artifact:

 

e7DJU.jpg

The drop-down item you can see on the above screen shot is not Yii feature! It is... A Firefox form autocomplete. No wonder I could not get this working tweaking my application... I feel like an idiot. The only advantage is I finally realized that :-)

 

Regards,

Share this post


Link to post
Share on other sites

Haha! I thought it was pretty weird the solution wasn't working. I thought it might have something to do with YII's active records implementation using some sort of reflection like behavior (on columns), but that would not really make sense; Why not offer the possibility for aliases? It would make no sense, as this is pretty much required functionality. (I considered diving into code again)

 

Not to rub it in, but consider doing to step by step development next time. If you had tested the functionality before trying to implement a full search function, you'd know the problem right away. A simple mock-up using GET and print_r() on the result would have done the job.

 

Lot's of interesting problems you have had lately. What are you working on here, if you are allowed to elaborate on it? :)

Share this post


Link to post
Share on other sites

I am writting sort of mailing campaigns manager for an addvertising agency. You know - an account starts a campaing, designer uploads his or her creation, customer can review and add his/her comments, these are in the campaing log. They feed the system with an e-mail address list, etc.

 

Lot of functionality. I am short in time already but hopefully it will speed-up soon. Delay was due to my 5-years old daughter got cold and was staying home nearly two weeks. I work at home, and it is impossible to get focus on work while you have such little girl everywhere around trying to pick you up for playing games all the time :-)

 

So really - your comments are helping me a lot in attacking problems. Thank you, Antonio - this is really very kind of you.

Share this post


Link to post
Share on other sites

And if you find some time you could look how to use virtual attributes in search criteria. My solution is based on pure SQL query passed to the database. And is less general than using true virtual attributes expressed in PHP. There must be some trick to do so.

 

In fact, search criteria do not anything more but "translate" some conventional code into a proper SQL query. This is why using virtual attribute in a "naive" way fails: there is no database column reflecting a virtual attribute. So, to overcome the issue sort of filter is necessary where appropriate abstracton should occure. A virtual database column must be assembled on the fly while executing a search query.

 

I am reading this article for instance:

 

http://www.yiiframework.com/wiki/117/using-standard-filters-in-cgridview-custom-fields/

 

it looks like the good one - isn't it?

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
Sign in to follow this  

×
×
  • Create New...