Yii2 Select Query : SQL Queries

Note

//Before use the Query Class, We have to add the following namespace

use yii\db\Query;
$connection = \Yii::$app->db;

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

queryAll

	
$model = $connection->createCommand('SELECT * FROM tbl_user');
$users = $model->queryAll();

queryOne

	
$model = $connection->createCommand("SELECT * FROM tbl_user where userid=$id");
$users = $model->queryOne();

queryColumn

	
$model = $connection->createCommand('SELECT username FROM tbl_user');
$users = $model->queryColumn();

queryScalar

	
$model = $connection->createCommand('SELECT COUNT(*) FROM tbl_user');
$users_count = $model->queryScalar();

Query With Prepared Statements()

	
$user = $connection
	->createCommand('SELECT * FROM tbl_user WHERE userid=:userid');
$user->bindValue(':userid', $_GET['userid']);
$model = $user->query();

Query Class

Sample 1:

$query = new Query;
$query->select('userid, username')
    ->from('tbl_user')
    ->limit(10);
$command = $query->createCommand();
$data = $command->queryAll();	

Sample 2:

$query = new Query;
$columns = [];
$columns[] = 'userid';
$columns[] = 'username';

$query->select($columns)
    ->from('tbl_user')
    ->limit(10);
$command = $query->createCommand();
$data = $command->queryAll();