Yii Framework 2 : Select Query For Model

This tutorial will help you to get the list of function to retrieve or select the data from the database using model of yii2.0 framework. I updated most of the functions it will helpful for yii2.0 select query method.

find()

Find a single record using primary key. find() method is used for this. If record is available it will return object else we will get false or null;

$model = User::find(1);
if($model){
    echo $model->username;
    echo $model->status;
}

select()

select() method is used to select the display columns from the tables of yii2;

$model = User::find()->select('column1, column2')->all();

all()

Get all the records from the database table using all() method of yii2.0 model.

$model = User::find()->all();

one()

Retrieve the single record from the database based on condition.

$model = User::find()->one();

where()

Sample 1:

$userid=1;
$model = User::find()
        ->where('userid > :userid', [':userid' => $userid])
        ->one();

Sample 2:

$model = User::find()
        ->where(['reg_date' => $date, 'status' => 1])
        ->one();

Sample 3:

$model = User::find()
        ->where("reg_date > '2014-01-01' and status=1")
        ->all();

Sample 4:

$model = User::find()
        ->where('userid > :userid', [':userid' => $userid])
        ->orWhere('primary_user = :primary_user', [':primary_user' => $primary_user])
        ->andWhere('status = :status', [':status' => $status])
        ->all();

Output Query

SELECT * FROM `tbl_user` WHERE ((userid > 1) OR (primary_user = 1)) AND (status = 1)

orderBy()

Sample 1:

$model = User::find()
        ->where(['status' => 1])
        ->orderBy('userid')
        ->all();

Sample 2:

$model = User::find()
        ->where(['status' => 0])
        ->orderBy('userid')
        ->one();

Sample 3:

$model = User::find()
        ->orderBy([
            'usertype'=>SORT_ASC,
            'username' => SORT_DESC,
        ])
        ->limit(10)
        ->all();

Output Query

SELECT * FROM `tbl_user` ORDER BY `usertype`, `username` DESC LIMIT 10

count()

$model = User::find()
        ->where(['status' => 0])
        ->orderBy('userid')
        ->count();

asArray()

$model = User::find()
        ->asArray()
        ->all();

$model = User::find()
        ->asArray()
        ->one();

indexBy()

$model = User::find()
        ->indexBy('id')
        ->one();
	

limit()

Sample 1:

$model = User::find()
        ->limit(10)
        ->all();
	

Sample 2:

$model = User::find()
        ->where('userid > 1 and isactive=1')
        ->limit(2)
        ->all();

offset()

$model = User::find()
        ->limit(5)
        ->offset(10)
        ->all();

Output Query

SELECT * FROM `tbl_user` LIMIT 5 OFFSET 10

Limit With Pagination

Here We limited the record using pagination class and we set the default page size. See the below code It contains total records count, default page size, limit and offset.

$query = Country::find();
$pagination = new Pagination([
            'defaultPageSize' => 5,
            'totalCount' => $query->count(),
        ]);
$countries = $query->orderBy('name')
        ->offset($pagination->offset)
        ->limit($pagination->limit)
        ->all();

LIKE Condition

Sample 1:

$model = User::find()
        ->where(['LIKE', 'username', 'admin'])
        ->all();
//OR
$model = User::find()
        ->where('username LIKE :query')
        ->addParams([':query'=>'%admin%'])
        ->all();

Sample 2:

$model = User::find()
        ->where(['NOT LIKE', 'username', 'admin'])
        ->all();

Output Query

SELECT * FROM `tbl_user` WHERE `username` LIKE '%admin%'
SELECT * FROM `tbl_user` WHERE `username` NOT LIKE '%admin%'

In Condition

Sample 1:

$model = User::find()
        ->where([
            'userid' => [1001,1002,1003,1004,1005],
        ])
        ->all();

Sample 2:

$model = User::find()
        ->where(['IN', 'userid', [1001,1002,1003,1004,1005]])
        ->all();

Output Query

SELECT * FROM `tbl_user` WHERE `userid` IN (1001, 1002, 1003, 1004, 1005)

Sample 3:

$model = User::find()
        ->where(['NOT IN', 'userid', [1001,1002,1003,1004,1005]])
        ->all();

Output Query

SELECT * FROM `tbl_user` WHERE `userid` NOT IN (1001, 1002, 1003, 1004, 1005)

between()

Sample 1:

$model = User::find()
        ->select('username')
        ->asArray()
        ->where('userid between 1 and 5')
        ->all();

Output Query

SELECT `username` FROM `tbl_user` WHERE userid between 1 and 5

groupBy()

$model = User::find()
        ->groupBy('usertype')
        ->all();

Output Query

SELECT * FROM `tbl_user` GROUP BY `usertype`

having()

$states=1;
$model = User::find()
        ->groupBy('usertypee')
        ->having('states >:states')
        ->addParams([':states'=>$states])
        ->all();

Output Query

SELECT * FROM `tbl_user` GROUP BY `usertypee` HAVING states >1

addParams()

Sample 1:

$usertype=1;
$model = User::find()
        ->where('usertype = :usertype')
        ->addParams([':usertype' => $usertype])
        ->one();

Sample 2:

$usertype=1;
$status=0;
$model = User::find()
        ->where('usertype = :usertype and status=:status')
        ->addParams([':usertype' => $usertype])
        ->addParams([':status' => $status])
// OR Multiple Assigns
// ->addParams([':usertype' => $usertype,':status' => $status])
        ->one();

Multiple Conditions

Sample 1:

$model = User::find()
        ->where([
            'type' => 26,
            'status' => 1,
            'userid' => [1001,1002,1003,1004,1005],
        ])
        ->all();

Output Query

SELECT * FROM `tbl_user` WHERE (`type`=26) AND (`status`=1) AND (`userid` IN (1001, 1002, 1003, 1004, 1005))

findBySql

Sample 1:

$sql = 'SELECT * FROM tbl_user';
$model = User::findBySql($sql)->all();

Sample 2:

$sql = 'SELECT * FROM tbl_user';
$model = User::findBySql($sql)->one();