Yii 1.1: relatedsearchbehavior

Behavior making searching relations in CGridView easier
27 followers

Creating CGridViews with related tables or getting fields from related tables is simplified with this behavior. It magically adds the needed 'with' clauses and provides aliases to fields of records in the relations.

Live demo

Uses the KeenActiveDataProvider extension to limit the number of requests to the database.

Requirements

Developped with Yii 1.1.12 - surely useable with earlier and later versions.

Forum

A Forum thread is available for this extension.

Usage

To use this extension you must do 5 steps:

  1. "Install" the RelatedSearchBehavior;
  2. Update the Model's search() method;
  3. Add/Update the 'behaviors()' method;
  4. Add the search fields to the Model rules as safe fields;
  5. Use the search field as any other field in the CGridView.

Here are the details for these steps:

1. "Install" the RelatedSearchBehavior;

Installation is as easy as adding the location to the 'import' configuration of the Yii configuration file (main.php):

return array(
         [...]
    'import'=>array(
            [...]
            'ext.KeenActiveDataProvider', // Alias to right location.
        'ext.RelatedSearchBehavior',  // Alias to right location.
    ),

In the above the RelatedSearchBehavior file was placed in the extensions directory.

2. Update the Model's search() method;

Update the overload of ActiveRecord::search(). The following example shows how you can set the default search order. You can set any parameter accepted by CActiveDataProvider (such as pagination) in the second array. So, replace code like this:

return new CActiveDataProvider($this, array(
  'criteria'=>$criteria,
  'sort'=>array(
    'defaultOrder'=>'title ASC',
  )
));

by code like this (I prefer using intermediate variables like '$sort' to improve readability and increase flexibility):

$sort=array(
    'defaultOrder'=>'title ASC',
);
return $this->relatedSearch(
    $criteria,
    array('sort'=>$sort)
);

In the above example, 'title' can be one of the related fields that you defined.

3. Add/Update the 'behaviors()' method

Attach the behavior to the CActiveRecord and specifies the related fields.

function behaviors() {
    return array(
        'relatedsearch'=>array(
             'class'=>'RelatedSearchBehavior',
             'relations'=>array(
                  'serial'=>'device.device_identifier',
                  'location'=>'device.location.description',
                   // Field where search value is different($this->deviceid)
                 'fieldwithdifferentsearchvalue'=>array(
                     'field'=>'device.displayname',
                     'searchvalue'=>'deviceid'
                  ),
                 // Next line describes a field we do not search,
                 // but we define it here for convienience
                 'mylocalreference'=>'field.very.far.away.in.the.relation.tree',
             ),
         ),
    );
}

4. Add the search fields to the Model rules as safe fields;

public function rules()
    {
        return array(
            [...]
            array('serial,location,deviceid','safe','on'=>'search'),
        );
    }

5. Use the search field as any other field in the CGridView.

For the CGridView column specification, you can then just put 'serial' for the column (no need to do 'name'=>..., 'filter'=>..., 'value'=>... .

Example:

$this->widget('zii.widgets.grid.CGridView', array(
  [...]
  'columns'=>array(
      [...]
      'serial',
      'location',
  )
));

6. To use "autoScope"

Autoscope allows you to search a field using a scope without declaring the scope yourself.

For instance, you can use this:

MyModel::model()->location('Belgium')->findAll();

Before, you have to: 1. Add RelatedSearchBehavior to the behaviors of your CActiveRecord (already done in the preceding steps, 2. Add the following generic code to your CActiveRecord Model.

/**
     * Add automatic scopes for attributes (uses RelatedSearchBehavior).
     */
    public function __call($name,$parameters) {
        try {
            return parent::__call($name,$parameters);
        } catch (CException $e) {
            if(preg_match(
                    '/'.Yii::t(
                            'yii',
                            quotemeta(
                                    Yii::t(
                                            'yii',
                                            '{class} and its behaviors do not have a method or closure named "{name}".'
                                            )
                                    ),
                                    array('{class}'=>'.*','{name}'=>'.*')
                            )
                    .'/',$e->getMessage())) {
                return $this->autoScope($name, $parameters);
            } else {
                throw $e;
            }
        }
    }

You are allowed to provide all the regular compare parameters:

MyModel::model()->location($searchvalue,$partialMatch,$operator,$escape)->findAll();

defaults are the same as compare: autoscope(,false,"AND",false).

This is usefull in complex nested conditions, not so much for simple searches like the above.

7. Using relations in CSort's attributes for sorting.

'CSort' allows you to specify 'virtual attributes' for sorting as mentioned in the Yii documentation. Without RelatedSearchBehavior, you must make sure that you include the relations used in the search condition. With RelatedSearchBehavior, you do not need to take care about that - the extension takes care about it for you (since 1.16).

$sort=array(
    'defaultOrder'=>'title ASC',
    'attributes'=>
        array(
            'price'=>array(
                'asc'=>'item.price',
                'desc'=>'item.price DESC',
                'label'=>'Item Price'
            ),
        ),
    );
return $this->relatedSearch(
    $criteria,
    array('sort'=>$sort)
);

The preferred approach is that you'ld use attributes defined for RelatedSearchBehavior, but this might be usefull in combined sort conditions:

$sort=array(
    'defaultOrder'=>'title ASC',
    'attributes'=>
        array(
            'groupprice'=>array(
                'asc'=>'item.group, item.price',
                'desc'=>'item.group DESC, item.price DESC',
                'label'=>'Item Price'
            ),
        ),
    );
return $this->relatedSearch(
    $criteria,
    array('sort'=>$sort)
);

8. Tips & notes

  • If you like RelatedSearchBehavior, you can create or update your Gii template to generate it automatically.
  • If you use 'ERememberFiltersBehavior', you must set the rememberScenario before getting the dataProvider - otherwise the relations will not be resolved in the sort clause. So you write:
$model->rememberScenario="admin"; // Must be before ->search().
$dataProvider=$model->search(); // Uses RelatedSearchBehavior

History

 * 1.03  Quoting relations in database.
 * 1.04  Added autoScope.
 *       Added option 'partialMatch' for relation.
 * 1.05  Enable multiple attributes in default sort.
 * 1.06  Fix to autoScope - return owner (chaining) + correct example in comment.
 * 1.07  Improved compatibility with ERememberFiltersBehavior ("standard" sort key).
 * 1.08  Fix in KeenActiveDataProvider for postgresql + updates to demo for postgresql.
 * 1.09  Allow array as search value.
 * 1.10  Use alias defined in model's relation (Svobik7)
 * 1.11  Corrected test of owner class type (lower case 'c') and improved error message 
 * 1.12  Autoscope for relations and 'addRelatedCondition' method as a complement to 'addCondition'.
 * 1.13  Handle 'getter' in autoscope call.
 * 1.14  Look recursively for relations for autoscope.
 * 1.15  Added 'getDataProvider'.
 * 1.16  Added relations used in sort "attributes" provided as a parameter.
 * 1.17  Improved error messages. Fix for relations that are defined through option array.
 *       Fix for local fields (aliases/virtual attributes with modified search field)
 * 1.18  Renamed 'addRelationCondition' in 'addRelatedCompare'.
 * 1.19  Reactivated 'together'.

Resources

Total 20 comments

#18609 report it
SomethingWicked at 2014/11/30 05:36am
New release

Thank you for this last update and for the extension :)

#18605 report it
le_top at 2014/11/29 04:25pm
New version with fix (see forum) -> 'together' has to be true.

I released version 1.19 in order to release a fix. This means that the released version jumps from v1.16 to 1.19.

My own use of this extension is getting more and more advanced so this "fixes" or "enables" uses that are probably beyond the use of most users.

Also, I included a famous debug extension in the demo so that it is easy to check the SQL statements that are built (open a page of the grid in the demo in a new window to see them).

Have fun with it ;-).

#18514 report it
le_top at 2014/11/08 04:42pm
1.16 released

I stumbled into restrictions with the use of relations in the CSort attributes, so I extended RelatedSearchBehavior to add the relations used in these attributes when needed.

I ended up using these attributes in a complex setup where using CSort attributes is better than adding relations. As I was using a CListView and it turned out that I had to add my 'virtual attributes' to the CSort attributes if I wanted them to be sortable through the 'sortableAttributes' property of the CListView.

While the extension is getting a bit more complex, the use is still simple.

#18478 report it
le_top at 2014/11/04 04:02pm
Next version to be even more powerfull.

For the selected number of followers and likers, the next version (1.15) will be even more powerfull. I just extended my version so that this is possible:

// Set some search conditions on the model (is_active and owner are autoscopes,
//  but they do not need to be active scopes).
// Reminder: these are autoscopes because $is_active and $theowner are
//           attributes or related fields of ConcreteModel,
//           and they are automatically provided by RelatedSearchBehavior.
ConcreteModel::model()->is_active(1)->theowner($owner_id);
 
// Set an order condition on a relation (!!!)
ConcreteModel::model()->getDbCriteria()->order='serial';
 
// Now create a nice dataprovider using these conditions and sort order on a relation.
//  (getDataProvider is provided by RelatedSearchBehavior 1.15).
$dataProvider=Tracker::model()->getDataProvider();
 
// Next, loop over the values from this search
$iterator=new CDataProviderIterator($dataProvider);
foreach($iterator as $data) {
   /* Do things with $data */
}

Isn't that nice ?!

#18448 report it
le_top at 2014/10/30 04:38am
Version 1.14

I just released version 1.14.

It adds some more power to the extension.

It also allows to use a get method on a relational attribute (getXXX(), where XXX is provided by RelatedSearchBehavior).

It's also less lower/upper case sensitive.

Finally, you can use 'addRelatedCondition' - but I should have called that 'addRelatedCompare' as it behaves like a '$model->compare()', but for a relational attribute. [So a TODO for me to rename that].

#18291 report it
le_top at 2014/10/12 12:26pm
"Recursive" relations

My local version (1.14) now looks "recursively" for relations. I.e.: when referencing a relation field that is itself a relation, it will be "expanded" too. Further, joins for "deep" relations were not always added and this is fixed too. If you want to test, let me know.

#18267 report it
le_top at 2014/10/08 04:59pm
'addCondition' for relation

Hi I just extended RelatedSearchBehavior in my "private" copy with 'autoscope' for relations and 'addRelatedCondition' to add a condition on a relation easily.

I am only releasing it in the near future if there is any real interest for that, otherwise it will be with some potential other future update.

#14506 report it
le_top at 2013/08/18 04:57pm
Allow array for search value

I updated the extension to allow arrays for the search value. I did not test with arrays, I only tested that the demo still works.

The test implemented previously was designed after the way the search method was generated with the default Gii generator.

#14493 report it
zvik2004 at 2013/08/17 11:43am
Fail when using array as a condition

Hi,
I tried to set an array to a ('related search') attribute and it failed in the line (because it's an array and not a string):

if("$search_value"!=="") {

I see that you check in the next line if it's an object:

if(!is_object($search_value)) {

Maybe you should move this check up?

Thanks again for great extension!
Zvi (keen user ;-)).

#13810 report it
le_top at 2013/06/27 05:13pm
Update for ERememberFiltersBehavior & sorting

To the followers: I just released an update so that ERememberFiltersBehavior can still rely on the expected naming of the sort $_GET key.

#13205 report it
le_top at 2013/05/14 02:33am
Discussion continued on forum

Please check the forum for the continuation of the discussion.

#13204 report it
hbalkhi at 2013/05/14 01:06am
Pagination issue

@le_top Thanks for taking time to reply , actually that's what i was planning to do but i thought maybe somebody faced the same issue ..anyway i found out that the the three order by clauses in the query (the outer and inner data sets query)that pick up the data are missing and i have no idea why ...

#13202 report it
le_top at 2013/05/13 07:26pm
Pagination issue

Hi In the demo the pagination behavior looks normal to me (using sqlite) and also on a production site (using mysql). This may have something to do with your gridview wrapper. I suggest that you check out the demo and "migrate" it to your bootstrap wrapper (using a new view). I think that this will help discover where the issue may be. Also check the SQL searches/requests made (using yiidebugtb extension for instance).

#13200 report it
hbalkhi at 2013/05/13 03:40pm
Pagination issue

thanks for this great extension..

i wanted to know if anybody facing issues with pagination where the data rows stay the same if you search using one of the related fields or sort by one of them and i noticed also that the indicators of the current page are changing but the data staying the same

everything works normally if i search using the fields of the same model i'm using crisu bootstrap grid and

appreciate any help

#13052 report it
le_top at 2013/04/30 02:13pm
Multi-attribute support for defaultOrder

Follow a request on the forum, I added multi-attribute defaultOrder support. So now the default order can be like this:

'sort'=>array('defaultOrder'=>'Artist DESC,TrackName ASC'),

where Artist and TrackName correspond to virtual attributes referring to fields in related tables (Related Search Behavior updates the defaultOrder as needed).

#12930 report it
le_top at 2013/04/21 12:57pm
Updated with partialMatch & autoScope

@zvik2004: I see that you are developing a relation as a keen user ;-).

I have added the partialMatch code (slightly differently), and 'autoScope' a feature that I started using myself.

#12927 report it
zvik2004 at 2013/04/21 07:33am
Great extension! (2) :-) - add parameter partialMatch

Hi

In your search, you use partialMatch=true. I think it will be great to get it as a parameter. I modified my code, please let me know what you think:

The configuration can be something like:

'fieldxyz'  => array(
    'field'         => 'table.field',
    'partialMatch'  => false,
),

And in the RelatedSearchBehavior file (in the appropriate places):

if(is_array($relationvar)) {
    $relationfield=$relationvar['field'];
    $search_value = isset($relationvar['searchvalue']) ? $this->getOwner()->{$relationvar['searchvalue']} : $this->getOwner()->{$var};;
    $partialMatch = isset($relationvar['partialMatch']) ? $relationvar['partialMatch'] : true;
} else {
    $relationfield=$relationvar;
    $search_value=$this->getOwner()->{$var};
    $partialMatch = true;
}
 
//and the search part:
$criteria->compare($column,$search_value,$partialMatch);

Thanks!

Zvi.

#12803 report it
le_top at 2013/04/12 04:10pm
? Your feedback on potential extension of RelatedSearchBehavior

Please check the Forum regarding a potential new feature to automatically add scopes for attributes.

#12647 report it
le_top at 2013/04/03 08:45am
? Why just one field

Hi Just one field because of the work (writing/testing) involved. Ideally, the code would accept multifield ordering in a single string specification or in an array specification. I am happy to integrate any full code.

#12645 report it
gb5256 at 2013/04/03 08:30am
Sorting on related record

Hi, thanks for extending your extension to work also with the default sort. Perfect. Just for curiosity: Why only for one field? Is this getting too complex otherwise? Don't get me wrong, I am totally happy with this, but I can think of cases where I need to do this for more than one field.

gb

Leave a comment

Please to leave your comment.

Create extension