How2Yii2 | คำสั่ง Query ใน Yii2

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();

ที่มา