find()
$model = User::find(1);2
    if($model){
        echo $model->username
        echo $model->status;
    }
$query2="SELECT *,111.045 * DEGREES(ACOS(COS(RADIANS(".$user->latitude.")) * COS(RADIANS(latitude)) * COS(RADIANS(longitude) - RADIANS(".$user->longitude.")) + SIN(RADIANS(".$user->latitude.")) * SIN(RADIANS(latitude)))) AS distance_in_km FROM items where is_available='Y' AND is_feature_ad='N' AND is_status='Y' AND is_deleted='N' ".$stating." having distance_in_km < 500 ORDER BY distance_in_km ASC";
      $items2 = Items::findBySql($query2)->count();

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

--------------------------------------------------------------------

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

--------------------------------------------------------------------

one()

$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

--------------------------------------------------------------------

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

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

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

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

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

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

--------------------------------------------------------------------

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

--------------------------------------------------------------------

Multiple Update
 User::updateAll(['activestatus' => 'Y'],'company_id='.$model->company_id);

 User::updateAll(array('activestatus' => 'Y',’'deletestatus' => 'N'’],'company_id='.$model->company_id);

--------------------------------------------------------------------

Multiple Delete
 SpService::deleteAll(['sp_id' => $model->sp_id]);

--------------------------------------------------------------------

where with Or Condition

$userdoctor = Appdoctor::find()
     ->where('email = :email', [':email' => $_REQUEST['email'] ])
     ->orWhere('mobile = :mobile', [':mobile' => $_REQUEST['email']])
     ->andWhere('password = :password', [':password' => sha1($_REQUEST['password'])])
 ->andWhere('login_type = :login_type', [':login_type' => 'N'])
     ->one();

SELECT * FROM `tbl_appdoctor` WHERE (((email = '9913327500') OR (mobile = '9913327500')) AND (password = '2aaa7257aaebb946c688905a351674cf099fd2f6')) AND (login_type = 'N')


--------------------------------------------------------------------

Group By Issue

SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));SET @@SESSION.SQL_MODE = 'NO_ENGINE_SUBSTITUTION';
Mysql 5.7 solve group by issue===============================
(1)sudo nano /etc/mysql/my.cnf
(2)add line
[mysqld]      sql_mode = "STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"(3)sudo service mysql restart

--------------------------------------------------------------------

Group By  with join sub query
SELECT * FROM tbl_userchating t1 JOIN (SELECT item_id, MAX(chating_id) chating_id FROM tbl_userchating WHERE (user_id=".$user->user_id." OR from_id=".$user->user_id.") ".$b."  GROUP BY item_id) t2 ON t1.chating_id = t2.chating_id AND t1.item_id = t2.item_id ORDER by t1.chating_id DESC

--------------------------------------------------------------------

Delete all raw foreign key not available

DELETE FROM emergencies WHERE emergency_category_id NOT IN (SELECT f.id FROM emergency_category f)

--------------------------------------------------------------------

Dynamic DB Connectoin
$connection = new \yii\db\Connection([
'dsn' => 'mysql:host=localhost;dbname=caperzwg_easyrider',
'username' => 'caperzwg_root',
'password' => 'admin@1234',
]);
$connection->open();

$options = $connection->createCommand('SELECT * FROM configurations WHERE eStatus = "Active"')->queryAll();
$language = $connection->createCommand('SELECT * FROM language_master WHERE eDefault = "Yes"')->queryOne();
@$vLanguageCode = $language['vCode'];
if($vLanguageCode == "")
{
    $vLanguageCode = "EN";
}
$languagelbl = $connection->createCommand('SELECT * FROM language_label WHERE vCode = "'.$vLanguageCode.'"')->queryAll();
$connection->close();
foreach ($options as $key => $configurations) {
    define($configurations['vName'],$configurations['vValue']);
}

foreach ($languagelbl as $key => $languagelbl) {
    define($languagelbl['vLabel'],$languagelbl['vValue']);
}

--------------------------------------------------------------------

Convert Timezone NOW()

$query="SELECT * FROM `user_order` WHERE order_status='D' AND is_send_fnotification = 'N' AND convert_tz(now(),@@session.time_zone,'+05:30') >= DATE_ADD(updated_at, INTERVAL 10 MINUTE)";

--------------------------------------------------------------------

Trigger Example

CREATE TRIGGER `admin_notification_add_product` AFTER INSERT ON `product`
 FOR EACH ROW INSERT INTO admin_notification_log
VALUES (
    NULL,
    "add_product",
    CONCAT((SELECT user_name FROM user WHERE user_id = NEW.user_id)," added new product"),
    NEW.product_id,
    convert_tz(now(),@@session.time_zone,'+00:00')
)
--------------------------------------------------------------------