Yii 1.1: How to add a named scope to ActiveRecords with a behavior

10 followers

Since Yii 1.0.5 you can use named scopes with ActiveRecords. They are a great help in simplifying your query calls.

For example, say we have a lot of tables with a CreatedAt column containing an integer UNIX timestamp. Now we want to add a named scope called between($start,$end) to all our ActiveRecords so that in the end we could simply use it like this:

$start=strtotime('2009-01-01');
$end=strtotime('2009-12-31');
Post::model()->between($start,$end)->findAll($someCriteria);

This should return all Posts, matching $someCriteria but only those created between $start and $end.

So let's first start with the named scope. Named scopes are usually defined in a method called scope in our ActiveRecord. Since our named scope has parameters, we need to add an explicit method instead:

public function between($start,$end)
{
    $this->getDbCriteria()->mergeWith(array(
        'condition'=>'CreatedAt BETWEEN :start AND :end',
        'params'=>array(':start'=>$start, ':end'=>$end)
    ));
    return $this;
}

We could add this method to an ActiveRecord and between would be ready to use. But as we want to have this feature for all our models, we will create a behavior instead. Therefore we put the code inside a class and modify it a little:

class BetweenBehavior extends CActiveRecordBehavior
{
    public function between($start,$end)
    {
        $this->Owner->getDbCriteria()->mergeWith(array(
            'condition'=>'CreatedAt BETWEEN :start AND :end',
            'params'=>array(':start'=>$start, ':end'=>$end)
        ));
        return $this->Owner;
    }
}

We should save this file as BetweenBehavior.php and put it somewhere where our application can find it (e.g. into an imported directory). Finally we have to add our behavior to all model files. To do that, we add a method called behaviors:

class Post extends CActiveRecord {
 
    // ...
 
    public function behaviors()
    {
        return array(
            'BetweenBehavior' => array('class'=>'BetweenBehavior')
        );
    }
 
    // ...
}

That's it. Our named scope is ready to use with Post. Feel free to improve the above code or come up with your own ideas for named scopes that are good candidates for a behavior.

some improvement:

class BetweenBehavior extends CActiveRecordBehavior
{
    /**
     * @var string
     * here you can config it if you have a different name from 'CreateAt'
     */
    public $attrCreateAt = 'CreatedAt ';
 
    /**
     * @param $start
     * @param $end
     * @param null $alias
     *         you can specify an alias here which can be the current ActiveRecord( normally is 't') or relation Ar 's alias(the relation name)
     * @return CActiveRecord
     */
    public function between($start,$end,$alias=null)
    {
        $alias = empty($alias)? $this->getOwner()->getTableAlias(false) : $alias;
        if(empty($alias)){
           $condition = $this->attrCreateAt.' BETWEEN :start AND :end';
        }else{
           $condition =  $alias.'.'.$this->attrCreateAt.' BETWEEN :start AND :end';
        }
 
        $this->Owner->getDbCriteria()->mergeWith(array(
            'condition'=> $condition,
            'params'=>array(':start'=>$start, ':end'=>$end)
        ));
        return $this->Owner;
    }
}
 
usage:
   class Post extends CActiveRecord {
 
    // ...
 
    public function behaviors()
    {
        return array(
            'BetweenBehavior' => array(
                 'class'=>'BetweenBehavior',
                 'attrCreateAt' = 'createTime',
              )
        );
    }
 
    // ...
}
 
// with relation query  ,the CreateAt is from Comment class. haven't test this funcitonality
$posts=Post::model()->between($start,$end,'comments')->with('comments')->findAll();

如何用行为(behavior)给数据记录(ActiveRecords)添加命名范围

从yii 1.0.5版本开始你可以使用ActiveRecords的命名范围(Name scopes),对于我们简化数据库的查询请求有很大的帮助,例如,你有很多带有"CreateAt"的数据表,它是一个整数的UNIX的时间戮,现在我们想添加一个命名范围 between($start,$end),我们数据记录都可以方便的使用如下查询方法。

$start = strtotime('2009-01-01');
$end = strtotime('2009-12-31');
Post::model()->between($start,$end)->findAll($someCriteria);

这个查询返回所有符合($someCriteria)条件并且创建时间是在$start和$end之间的Post。

下面开始来学习命名范围,Named scopes 通常定义在ActiveRecord的一个叫做"scope"的方法内,因为我们的(Name scope)有参数,所以我们需要定义一个更加明确的方法。

public function between($start,$end){
   $this->getDbCriteria()->mergeWith(array(
       'condition'=>'CreatedAt BETWEEN :start AND :end',
       'params'=>array(':start'=>$start,':end'=>$end)
   ));
   return $this;
}

我们可以把这个方法添加到一个(ActiveRecord),between这个方法就可以被这个ActiveRecord使用了,但是如果我们想让我们的所有models都有个有功能,我们就可以使用(behavior),我只需要把上面的代码添加到一个Class里面简单修改一下就行了。

class BetweenBehavior extends CActiveRecordBehavior{
      public function between($start,$end){
             $this->Owner->getDbCriteria()->mergeWith(array(
                  'condition'=>'CreatedAt BETWEEN :start AND :end',
                  'params'=>array(':start' =>$start,':end' =>$end)
             ));
             return $this->Owner;
      }
}

我们可以把这个保存为一个文件,BetweenBehavior.php,把它放到一个程序可以自动导入的文件夹中(可导入文件夹),最后我们必须向我们所有的model添加这个行为,要实行这个功能我们向model添加一个behaviors方法。

class Post extends CActiveRecord{
   public function behaviors(){
          return array(
              'BetweenBehavior' => array('class' => 'BetweenBehavior')
          );
   }
}

就是这样了,我们的named scope就这样添加到Post里面了。随意改善以上代码,你可以有自己更好的想法来利用named scope。对于behavior这是一个好的候选方法。

Total 2 comments

#15388 report it
danschmidt5189 at 2013/11/04 04:26pm
Use CActiveRecord::getTableAlias() to disambiguate column names

Just for anyone stumbling on this, don't use the class name of the owner record to differentiate relations.

Use CActiveRecord::getTableAlias():

$this->owner->dbCriteria->mergeWith(array(
    'condition'=>"{$this->owner->tableAlias}.colName = :colName",
    'params' =>array(':colName' =>$someValue),
));
return $this->owner;
#1663 report it
agentshark at 2009/05/17 12:05pm
Same column name in multiple tables

If you use AR::with() for eager loading then you might have ambiguous column names. I solved this in the behavior using:

$ocn = get_class($this->Owner);
$this->Owner->getDbCriteria()->mergeWith(array(
    'condition'=>"$ocn.colName",
));
return $this->Owner;

Leave a comment

Please to leave your comment.

Write new article
  • Written by: Mike
  • Updated by: yiqing95
  • Category: Tutorials
  • Yii Version: 1.1
  • Votes: +4
  • Viewed: 15,242 times
  • Created on: Apr 30, 2009
  • Last updated: Dec 2, 2011