Active Record

Update: Check out our new Yii2 Developer Exchange site at It’s devoted exclusively for Yii2 developers to make it easier to find news, tips, tutorials and extensions related to Yii2.

Active Record

I’ve found it helpful to keep a list of example database code that I use in Yii. I hope this is useful to you.

Find Single Row By Attributes

$sender = User::model()->findByAttributes(array('email'=>$sender_email));

Find Multiple Row By Attributes

$sender = UserHistory::model()->findAllByAttributes(array('email'=>$sender_email));

Find Multiple Rows Using Conditions

$replies=Post::model()->findAll(array('condition'=>'parent_id=:parent_id','params'=>array(':parent_id' => $id),'order'=>'id desc'));

Query with Join

$message = Yii::app()->db->createCommand()
         ->from(Yii::app()->getDb()->tablePrefix.'post p')
         ->join(Yii::app()->getDb()->tablePrefix.'place q', '')
         ->join(Yii::app()->getDb()->tablePrefix.'users u', '')
         ->where('', array(':id'=>$i['post_id']))

Insert with Existence Check

if (!$this->exists('user_id = :user_id and  = :block_id',array(':user_id'=>$user_id,':block_id'=>$block_id))) {
  $block=new block;
  $block->created_at = new CDbExpression('NOW()'); 


Updates Period table rows where periodStart=0:



Here are some example standard scoping functions on conditions or sorting with limits. Example usage to find all rows in the Apple table where status = 1:

    public function scopes()
        return array(
                'order'=>'create_time DESC',

Here are some examples of using parameters with scopes:

// last $limit items sorted desc by create_time
public function recently($limit=5)
        'order'=>'create_time DESC',
    return $this;

// get members of Place table using a Left Join on user_id to Member table
public function memberOf($user_id)
    $this->getDbCriteria()->mergeWith( array(
      'alias' => 'Place',
      'join' => 'LEFT JOIN geo_member ON geo_member.place_id =',
      'condition'=>'geo_member.user_id='. $user_id,
      return $this;

Delete on Condition

Deletes all rows whose place_id = $place_id


Counting Rows

Counts rows whose criteria meet the following conditions:

$count = Membership::model()->count('member_id=:member_id and mglist_id<>:list_id', array('list_id'=>$id,':member_id'=>$member['member_id']));

Debugging Save Errors

When ActiveRecord save method fails, you can get a summary of errors:

if (!$model->save()) {
  var_dump ($model->getErrors());

Debugging SQL

The following shows an example of dumping SQL code and query parameters:

$pg = Yii::app()->db->createCommand()
   ->select('AsText(region) as region, AsText(center) as center')
   ->where('place_id=:place_id', array(':place_id'=>$id));

Leave a reply

Your email address will not be published. Required fields are marked *