Yii DB built in functions – Reference

Quick reference – Yii inbuilt functions – DB related
—————————————————-

###### Active Record functions: ############
Different ways to query
// 1st way
$criteria=new CDbCriteria;
$criteria->select=’title’; // only select the ‘title’ column
$criteria->condition=’postID=:postID’;
$criteria->params=array(‘:postID’=>10);
$post=Post::model()->find($criteria);

// 2nd way
$post=Post::model()->find(array(
‘select’=>’title’,
‘condition’=>’postID=:postID’,
‘params’=>array(‘:postID’=>10),
));

//Other ways
// Find All
// find all rows satisfying the specified condition
$posts=Post::model()->findAll($condition,$params);
// find all rows with the specified primary keys
$posts=Post::model()->findAllByPk($postIDs,$condition,$params);
// find all rows with the specified attribute values
$posts=Post::model()->findAllByAttributes($attributes,$condition,$params);
// find all rows using the specified SQL statement
$posts=Post::model()->findAllBySql($sql,$params);

User::model()->findAll(‘first_name=? AND last_name=?’, array(‘Paul’, ‘Smith’));
User::model()->findAllByAttributes(array(‘first_name’=>’Paul’, ‘last_name’=>’Smith’));

// find one
$post=Post::model()->find($condition,$params);
$post=Post::model()->find(‘postID=:postID’, array(‘:postID’=>10));

// find by primary key
$post=Post::model()->findByPk($postID,$condition,$params);

// find by attributes
$post=Post::model()->findByAttributes($attributes,$condition,$params);
$post=Post::model()->findByAttributes();

// find by specified SQL statement
$post=Post::model()->findBySql($sql,$params);

// count
$n=Post::model()->count($condition,$params);

// CREATE : creating record
$post=new Post;
$post->title=’sample post’;
$post->content=’content for the sample post’;
$post->create_time=time(); // $post->create_time=new CDbExpression(‘NOW()’);
$post->save();

// UPDATE: Updating Record
$post=Post::model()->findByPk(10);
$post->title=’new post title’;
$post->save(); // save the change to database

// DELETE: Delete record
$post=Post::model()->findByPk(10); // assuming there is a post whose ID is 10
$post->delete();

// Scopes
how to make use of scopes
In your model make a entry as below
// scope to get the 5 records of status=1 and recently updated
public function scopes()
{
return array(
‘published’=>array(
‘condition’=>’status=1’,
),
‘recently’=>array(
‘order’=>’create_time DESC’,
‘limit’=>5,
),
);
}

And then use the below statement to query.
$posts=Post::model()->published()->recently()->findAll();

// write in model class to to some ops before saving
public function beforeSave() {
if ($this->isNewRecord)
$this->created = new CDbExpression(‘NOW()’);
else
$this->modified = new CDbExpression(‘NOW()’);
// anything else.. like, date modification, time modification, etc..

return parent::beforeSave();
}

######## Query builder functions: ############
// NEXT : sql command writing
$command = Yii::app()->db->createCommand(‘SELECT * FROM tbl_user’);

// fetch row
$user = Yii::app()->db->createCommand()
->select(‘id, username, profile’) // to fetch all use ->select(‘*’)
->from(‘tbl_user’) // for multiple table use as ->from(array(‘tbl_user’, ‘tbl_profile’))
->where(‘id=:id’, array(‘:id’=>$id))
->queryRow();
OR
$row = Yii::app()->db->createCommand(array(
‘select’ => array(‘id’, ‘username’),
‘from’ => ‘tbl_user’,
‘where’ => ‘id=:id’,
‘params’ => array(‘:id’=>1),
))->queryRow();

// Fetch row using join
$user = Yii::app()->db->createCommand()
->select(‘id, username, profile’)
->from(‘tbl_user u’)
->join(‘tbl_profile p’, ‘u.id=p.user_id’) // join, leftJoin, rightJoin, crossJoin, naturalJoin
->where(‘id=:id’, array(‘:id’=>$id))
->queryRow();

// fetch all
$user = Yii::app()->db->createCommand()
->select(‘id, username, profile’)
->from(‘tbl_user’)
->where(‘id=:id’, array(‘:id’=>$id))
->limit(10, 20) // limit 10 and offset 20
->queryAll();

$users = Yii::app()->db->createCommand()
->select(‘*’)
->from(‘tbl_user’)
->queryAll();

// display SQL statement
$sql = Yii::app()->db->createCommand()
->select(‘*’)
->from(‘tbl_user’)
->text;

// INSERT
// build and execute the following SQL:
// INSERT INTO `tbl_user` (`name`, `email`) VALUES (:name, :email)
$command->insert(‘tbl_user’, array(
‘name’=>’Tester’,
’email’=>’tester@example.com’,
));

// UPDATE `tbl_user` SET `name`=:name WHERE id=:id
$command->update(‘tbl_user’, array(
‘name’=>’Tester’,
), ‘id=:id’, array(‘:id’=>1));

// DELETE FROM `tbl_user` WHERE id=:id
$command->delete(‘tbl_user’, ‘id=:id’, array(‘:id’=>1));

// NEXT
// create a new entry with time modification example
$post=new Post;
$post->title = “Some title”;
$post->desciption = “Some description”;
$post->create_time=new CDbExpression(‘NOW()’);
// $post->create_time=’NOW()’; will not work because
// ‘NOW()’ will be treated as a string
$post->save();