How to write sql query in yii2

Question

I have a raw sql query which i run using yii2 Yii::$app->db->createCommand() but then i wonder i how i can use yii's method of writing queries to actualize the same thing.

 $m = "SELECT p.basic_plan_amt AS basic,p.premium_plan_amt AS premium,p.daju_plan_amt AS daju,c.name 
 AS country FROM price p LEFT JOIN country c ON p.country_id = c.id WHERE p.country_id = " .$country_id; 

though the above query works well and provide expected result, but then how can i write the same query using the below format

$model = \backend\models\Price::find()->select([p.basic_plan_amt AS basic,p.premium_plan_amt AS 
premium,p.daju_plan_amt AS daju,c.name 
AS country])->where(['country_id' => $country_id])->all();

Answer 1

use createcommand() method:

use yii\db\Query(); 
$connection = \Yii::$app->db; 
$query = new Query; 
$insql = $connection->createCommand("SELECT* FROM inventory );  
$result=$insql->queryAll();

the above method list all data from the inventory table.

Answer 2

Below are the examples of query in yii hope it will help you More reference

Relation Model

$model = User::find()
        ->with('comments')
        ->all();
foreach ($model as $user) {
    // get data from relation model
    $comments = $user->comments;
        ......
    foreach($comments as $comment){
        ........
    }
}

joinWith() Sample 1:

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

Sample 2:

$model = User::find()
        ->joinWith('comments')
        ->orderBy('tbl_comments_id.id, tbl_user.id')
        ->all();

innerJoinWith()

$model = User::find()
        ->innerJoinWith('comments', false)
        ->all();
// equivalent to the above
$model = User::find()
        ->joinWith('comments', false, 'INNER JOIN')
        ->all();

Join()

JOIN_TYPE = INNER JOIN, LEFT OUTER JOIN, RIGHT OUTER JOIN, FULL OUTER JOIN etc
Syntax


$query = new Query;
$query  ->select(['SELECT COLUMNS'])  
        ->from('TABLE_NAME_1')
        ->join( 'JOIN_TYPE', 
            'TABLE_NAME_2',
            'TABLE_NAME_2.COLUMN =TABLE_NAME_1.COLUMN'
        ); 
$command = $query->createCommand();
$data = $command->queryAll();

Sample 1:

$query = new Query;
$query  ->select([
        'tbl_user.username AS name', 
        'tbl_category.categoryname as  Category',
        'tbl_document.documentname']
        )  
        ->from('tbl_user')
        ->join('LEFT OUTER JOIN', 'tbl_category',
            'tbl_category.createdby =tbl_user.userid')      
        ->join('LEFT OUTER JOIN', 'tbl_document', 
            'tbl_category.cid =tbl_document.did')
        ->LIMIT(5)  ; 

$command = $query->createCommand();
$data = $command->queryAll();

Output Query

SELECT `tbl_user`.`username` AS `name`, `tbl_category`.`categoryname` AS `Category` 
FROM `tbl_user` 
LEFT OUTER JOIN `tbl_category` ON tbl_category.createdby =tbl_user.userid 
LEFT OUTER JOIN `tbl_document` ON tbl_category.cid =tbl_document.did 
LIMIT 5

leftJoin() Sample 1:

$query = new Query;
$query  ->select(['tbl_user.username AS name', 'tbl_category.type as Category'])  
        ->from('tbl_user')
        ->leftJoin('tbl_category', 'tbl_category.createdby = tbl_user.userid')
        ->limit(2); 

$command = $query->createCommand();
$data = $command->queryAll();

Output Query

SELECT `tbl_user`.`username` AS `name`, `tbl_category`.`type` AS `Category`
FROM `tbl_user` 
LEFT JOIN `tbl_category` ON tbl_category.createdby = tbl_user.useridd 
LIMIT 2