How to use SQL commands in yii2

Yii 2.0 General Discussions
1

i would like to use sql commands like

[size=2](SELECT * FROM movie_shows WHere start_date<=‘2015-05-14’ and[/size]

end_date>=‘2015-05-14’ and movie_id=1 )

UNION

(SELECT * FROM movie_shows WHere start_date<=‘2015-05-14’ and

end_date IS NULL and movie_id=1 )

in my contoller. i can’t use them directly in a controller, could you direct me how to use them

thanks

2

Hi!

From: http://www.yiiframework.com/doc-2.0/guide-db-active-record.html




// returns all inactive customers

$sql = 'SELECT * FROM customer WHERE status=:status';

$customers = Customer::findBySql($sql, [':status' => Customer::STATUS_INACTIVE])->all();

But doing the query in controller is bad practise, I think…

Better create a function including the query in model.

And call that function in controller…

So you have all DB interactions in your model.

Best Regards

3

use createcommand

$connection = \Yii::$app->db;

  &#036;command = &#036;connection-&gt;createCommand('SELECT * from table ');


  &#036;result = &#036;command-&gt;queryAll();

or you can this also

  use yii&#092;db&#092;Query; //import this first 


  &#036;query = new Query();


  &#036;query = Student::find()    //student is model class


  -&gt;select(['attributes','...']) // if u need specific fields


  -&gt;where('condition')


  -&gt;asArray()


  -&gt;all();
4

thanks :)