Update: Check out our new Yii2 Developer Exchange site at http://yii2x.com. 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()
         ->select('p.*,q.slug,q.prefix,u.eid')
         ->from(Yii::app()->getDb()->tablePrefix.'post p')
         ->join(Yii::app()->getDb()->tablePrefix.'place q', 'q.id=p.place_id')
         ->join(Yii::app()->getDb()->tablePrefix.'users u', 'u.id=p.author_id')
         ->where('p.id=:id', array(':id'=>$i['post_id']))
         ->queryRow();

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->user_id=$user_id;
  $block->block_id=$block_id;
  $block->created_at = new CDbExpression('NOW()'); 
  $block->save();

Updates

Updates Period table rows where periodStart=0:

$this->update('Period',array('periodStart'=>time(),'periodStartPoints'=>2500),'periodStart=0');

Scopes

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:

Apple::model()->published()->findAll();

    public function scopes()
    {
        return array(
            'published'=>array(
                'condition'=>'status=1',
            ),
            'recently'=>array(
                'order'=>'create_time DESC',
                'limit'=>5,
            ),
        );
    }

Here are some examples of using parameters with scopes:

// last $limit items sorted desc by create_time
public function recently($limit=5)
{
    $this->getDbCriteria()->mergeWith(array(
        'order'=>'create_time DESC',
        'limit'=>$limit,
    ));
    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 = Place.id',
      'condition'=>'geo_member.user_id='. $user_id,
    ));
      return $this;
  }

Delete on Condition

Deletes all rows whose place_id = $place_id

Member::model()->deleteAll('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')
   ->from(Yii::app()->getDb()->tablePrefix.'place_geometry')
   ->where('place_id=:place_id', array(':place_id'=>$id));
var_dump($pg->getText());
var_dump($pg->params);