Yii2 condition with subquery

Question

How do you write this SQL query in Yii2?

SELECT * FROM table
WHERE column1 IN (SELECT column2 FROM table WHERE column1 = 5)

Answer 1

Let's say your model named YourModel representing table with name table. Then the query will be:

$subQuery = YourModel::find()->select('column2')->where(['column1' => 5]);
$query = YourModel::find()->where(['column1' => $subQuery]);
$models = $query->all();

Also similar question was asked before.

Answer 2

Yii::$app->db->createCommand('SELECT * FROM table
       WHERE column1 IN (SELECT column2 FROM table WHERE column1 = :val)', [':val' => 5])->queryAll();

Will return associative array, like ['id' => 1, 'name' => 'Victor']

Answer 3

Yii2 SubQuery:

$query=YourBaseModel::find()->select('p_id');
$subquery=YourSubMOdel::find()->Where(['in','p_id',$query])->andWhere(['column1'=>1])->one();

$query=>select the p_id value in YourBaseModel. p_id value stored in $query variable. And then check the $query value in YourSubModel.

Answer 4

You can do it:

$query = 'SELECT * FROM table
          WHERE column1 IN (SELECT column2 FROM table WHERE column1 = 5)';

$list = $model::findBySql($query)->all();

Or:

$subquery = $model::find()->select('column1');
$query = $model::find()->where(['not in',$subquery])->all();

In this case it's the same. I hope I help you

Answer 5

This is a lot of ways for solving your issue. I will give an example of the most correct, in terms of use the yii2 framework.

SQL:

SELECT * 
FROM table 
WHERE 
    column1 IN 
    (SELECT column2 
        FROM table
        WHERE column1 = 5)

Yii2 activerecord:

$subQuery = TableModel::find()->select('column2')->where(['column1' => 5]);
$result = TableModel::find()->where(['IN', 'column1', $subQuery])->all();