Monday, 29 August 2016

How to perform database operations in yii2 using model.

In my previous post link i'll show how query builder we used for the sql statements. But here i'm explain how to used Model for sql operations.In this tutorial i'm using the active record class for the most common sql operation like select,update,delete etc.

Yii2 Active Record is the support for various new backends including NoSQL based ones. These backends include elasticsearch, redis, Sphinx search as well as mongodb. This is great news as we can switch backends without touching our code provided all our code uses Active Records for DB interaction.

Create a model class.


namespace frontend\models;

use Yii;

 * This is the model class for table "customers".
class Customer extends \yii\db\ActiveRecord
    const STATUS_DELETED = 0;
    const STATUS_ACTIVE = 1;
     * @inheritdoc
    public static function tableName()
        return 'customers';


Fetching Data in Yii2 using Active Record:

// to retrieve all customers and order them by their ID:
$customers = Customer::find()
    ->where(['status' => Customer::STATUS_ACTIVE])

// to return a single customer whose ID is 1:
$customer = Customer::find()
    ->where(['id' => 1])

// to return the number of *active* customers:
$count = Customer::find()
    ->where(['status' => Customer::STATUS_ACTIVE])

// to index the result by customer IDs:
$customers = Customer::find()->indexBy('id')->all();
// $customers array is indexed by customer IDs

// to retrieve customers using a raw SQL statement:
$sql = 'SELECT * FROM customer';
$customers = Customer::findBySql($sql)->all();

// to return a single customer whose ID is 1:
$customer = Customer::findOne(1);

// to return an *active* customer whose ID is 1:
$customer = Customer::findOne([
    'id' => 1,
    'status' => Customer::STATUS_ACTIVE,

// to return customers whose ID is 1, 2 or 3:
$customers = Customer::findAll([1, 2, 3]);

// to return customers whose status is "deleted":
$customer = Customer::findAll([
    'status' => Customer::STATUS_DELETED,

Featured post

How to create dynamic schema in mongodb

Sometime what we need in our application is to create mongodb schema for collection and some of our collection schema are same but names are...

Popular Posts