Yii 2.0 db createCommand()
In this post I want to talk about db createCommand() and using it to fetch records instead of find(). So far you have learnt how to use find() and using other functions such as joinWith() to join two tables and finally fetch records you need.
In this post I want to talk about db createCommand() and using it to fetch records instead of find().
So far you have learnt how to use find() and using other functions such as joinWith() to join two tables and finally fetch records you need.
Sometimes your query is very complicated and you cannot use those functions to select records. So what should you do?
The answer to this question is simply using createCommand(). I assume you all know how to use SQL syntax to fetch records. So You can write your query in phpMyAdmin and check to see if your query works fine or not. Since your query is a bit hard to write using find() and other functions, you should use createCommand() and your original query (the one you checked in phpMyAdmin) to fetch needed records.You can use createCommand() like:
"Yii::$app->db->createCommand($command, $params);"
$command is a string in which you have written your query, and the $params is an array of parameters you want to bind to your query. For example writing a simple SELECT query using createCommand() is:
"Yii::$app->db->createCommand('SELECT * FROM Person');"
In the code above, Person is our table name. Now the following query need parameters as binded parameters.
"SELECT * FROM Person WHERE pid = $pid"
Here pid is Person's primary key. So the query above changes to:
"Yii::$app->db->createCommand('SELECT * FROM Person WHERE pid = :pid', [ ':pid' => $pid,]);"
So you have learned how to use createCommand(). But we are not finished! These will not return records, you need to call another function to run this query and that is ->queryAll() or ->queryOne().
So, if you want to fetch all records do the following:
"Yii::$app->db->createCommand('SELECT * FROM Person WHERE pid = :pid', [ ':pid' => $pid,])->queryAll() ;"
And for one record you can use:
"Yii::$app->db->createCommand('SELECT * FROM Person WHERE pid = :pid', [ ':pid' => $pid,])->queryOne() ;"