Yii Framework 2.0 Tutorial

Yii Framework 2.0 Select Query : 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();	
  • Pete WK

    You should correct Scopes section, Scopes was implemented in another way in Yii2 according to Yii2 documentation http://www.yiiframework.com/doc-2.0/guide-db-active-record.html

    • m bala

      Thank you ….

      • Николай Степанов

        ))

  • Dario

    LIKE condition should be without %%
    User::find()->where([‘LIKE’, ‘username’, ‘admin’])->all()
    or
    User::find()->where(‘username LIKE :query’)->addParams([‘:query’=>’%admin%’])->all()

    • m bala

      Thank you Dario, I updated the code

      • Dario Trbović

        No problem 😉
        Your article helped me to understand Yii2’s ActiveRecord better

  • Alex Sergey

    “public static function olderThan”. static method? you sure? $this in static method?

  • Ben

    Would it be psosible to know which base class you use for all the queries on your website please as I keep getting errors each time I paste your code on a page, I cannot always work out which base class you use(beginner sorry).

  • Oscar Parra

    I need help, I want to do NEXT
    SELECT count(status) as cantidad, status FROM Table1 WHERE inventario =”VENTURA” group by status

    I try
    $result= Table1::find()
    ->select(‘COUNT(‘.$nombreColumna.’) AS cantidad, ‘.$nombreColumna)

    ->where($where)

    ->groupBy([$nombreColumna])

    ->count();

  • Abdullah

    Amazing 🙂 Very well , I didn’t find the documentation any where just like you have wrote. Best yii2 model documentation. THumbs up

  • Mena Developer

    Very good thank you so much