Yii Framework 2 : SQL Select Query


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

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


Sample 1:

$sql = 'SELECT * FROM tbl_user';
$model = User::findBySql($sql)->all();
Sample 2:

$sql = 'SELECT * FROM tbl_user';
$model = User::findBySql($sql)->one();


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


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


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


$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')
$command = $query->createCommand();
$data = $command->queryAll();
Sample 2:

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

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

  • 01011

    When using Query class, it is not necessarily to call createCommand() by yourself, $query->all() do it for you.

  • Luis M

    Hi, i need help As complete a combobox with data from the db?

    • m bala

      Try this
      echo $form->field($model, ‘name’)->dropDownList($listData, [‘prompt’=>’Choose…’]);

      • Luis M

        ty im use field($model, ‘atributename’)->dropDownList(ArrayHelper::map(model::find()->all(), ‘valatributemodel1’, ‘valatributemodel2’)) ?>

  • Saravanan Samidurai

    how to print values

  • Vipin Kumar


  • shiv

    hi guys
    i am in problome fetch two or more tables data in a single page in yii2

  • Nikhilesh Gupta

    SELECT t1.name
    FROM table1 t1
    WHERE t1.id = t2.table1_id
    FROM table2 t2
    WHERE t2.number =67
    how to write this query in active record???

    • Maksim

      * @return array
      public static function getReferrerList()
      $subQuery = (new Query())
      ->from(self::tableName() . ‘ t1’)
      ->where(‘”t1″.”referrer_user_id” = “‘.self::tableName().'”.”id”‘)
      $query = (new Query())
      ‘count’ => $subQuery,
      ‘count’ => SORT_DESC,
      // list ($sql, $params) = self::getDb()->queryBuilder->build($query);
      $result = $query->all();
      return $result;

  • tds

    very thx, it’s working:
    $connection = Yii::$app->db;
    $model = $connection->createCommand(“SELECT QUARTER(‘$date’)”);

    $users_count = $model->queryScalar();

  • kk

    join query for two tables.

  • Ajith

    how to use subqueries in yii2?

  • parthi

    how to show a particular tabledata in database using yii2 framework.

  • Masdi

    Hi, how to generate query like this:
    “SELECT fieldName FROM tableName WHERE fieldName LIKE ‘ADX%LZU’ “

  • Vinai Raj U

    how to insert multiple commands into a prepared statement?
    any one please Help me to do this