finding activerecords based on virtual attributes

Yii 1.1 General Discussion
1

Dear all,

I have a model with 3 fields: category, group, title.

users can select records of this model by text search. texts entered by users should be searched in all fields and matching records should be displayed to user via autocomplete.

in model class, I created a virtual attribute as follows:




        public function getText()

        {

            return $this->category.' '.$this->group.' '.$this->title;

        }

and in autocompleteAction:




        $criteria->addSearchCondition('text', $_GET['term']);

        $models = $model::model()->findAll($criteria);

as you can see, i used ‘text’ as search condition attribute expecting to use getText(), but findAll() creates and excecutes a DB query and wont look at model attributes at all, so an error occurs stating ‘text’ is not defined in table.

I can make autocompleteAction specific to this model to handle the issue, but i need it to be generic. i.e. action take model and attribute as parameters and return matching list, no matter attribute is in table or is a virtual one defined in model.

any help would be appreciated.

2

I believe you should use 3 conditions, one for each field, and get the results normally, then format them as required for displaying (make matching patterns bold etc)

3

Yes, I’m doing this right now, but grouping all attributes in model as a virtual one, is more standard.

also, I use some characters to show between 3 strings, like ‘-’ or ‘\’ or space, and this should be determined in model class, but using 3 conditions, I have to set this connecting character in autocompleteAction, which is not it’s business.

4

I think it will never work with virtual attributes as they are created “on the fly” AFTER the model is loaded, so you can’t use them when searching. If your problem is merely related to the fact that your models will grow and the SQL queries quickly become complex than one idea could be to index your models with a search engine like Sphinx, Solr or ElasticSearch. There is a Sphinx extension for Yii out there and I use ElasticSearch a lot these days. This way you can use search functionalities that are (usually) superior to SQL fulltext searches.

5

You’d need to add to your select portion of the criteria something like:




$criteria->select = "t.*, CONTACT( `category`,' ',`group`,' ',`title`) as text";

because you’re asking mysql to interpret something that it doesn’t know how to see.

6

@Haensel and @Dana, thank you for your help, I learned from your posts.

finally, I managed to handle the issue. logically, my model fields make a tree hierarchy. i.e. each title belongs to a group and each group belongs to a category. so, I change my table to an adjacency list(child-parent style), and use DB’s built in recursive query to get parents of each element that user selects (I use Postgresql, to achieve the same functionality in MySql, somebody can use nestedsetbehavior)