CODEWITHSUNDEEP
yiiyii2
Ngoun Thavy
Ngoun Thavy

Reputation: 447

Yii2 subquery in Active Record

How can I convert this sql into active record query

SELECT * FROM `base_twitter` WHERE id NOT IN (SELECT base_id from base_followers)

Upvotes: 23

Views: 41645

Answers (3)

Yasin Patel
Yasin Patel

Reputation: 5731

Try This Query 

$models = BaseTwitter::find()->where('id NOT IN (SELECT base_id from base_followers)')->all();

Upvotes: 3

Oleg
Oleg

Reputation: 7377

// SELECT * FROM (SELECT * FROM `user` WHERE `active` = 1) `activeusers`;
$subquery = (new \yii\db\Query)->from('user')->where(['active' => true])
$query = (new \yii\db\Query)->from(['activeusers' => $subquery]);

// subquery can also be a string with plain SQL wrapped in parenthesis
// SELECT * FROM (SELECT * FROM `user` WHERE `active` = 1) `activeusers`;
$subquery = "(SELECT * FROM `user` WHERE `active` = 1)";
$query = (new \yii\db\Query)->from(['activeusers' => $subquery]);

Upvotes: 13

arogachev
arogachev

Reputation: 33538

Assuming that your models are named BaseTwitter and BaseFollower accordingly, this should work:

$subQuery = BaseFollower::find()->select('id');
$query = BaseTwitter::find()->where(['not in', 'id', $subQuery]);
$models = $query->all();

Upvotes: 68

Related Questions