yii2-Active record with not in condition

Question

I am working on yii2. I am using active record for searching a reference number. The query is below

$q = isset($_GET['q']) ? $_GET['q'] : '';
    if(empty($q)) exit;
    $ser = Survey::find()->where("ref_no like '%$q%'")->andWhere(['status'=>1])->asArray()->all();

    return json_encode($ser);

The above query will get all the reference numbers which are in survey table. Now I want to add a NOT IN condition. The raw query is below

...... where ref_no LIKE '%$q%' NOT IN (select ref_no from installations where ref_no LIKE '%q%')

How can I add this to my active record query?

Any help would be highly appreciated.

Possible duplicate of [Yii2 subquery in Active Record](https://stackoverflow.com/questions/30164491/yii2-subquery-in-active-record) — Sfili_81, Apr 13 '18 at 06:57

score 2 · Answer 1 · edited Apr 13 '18 at 09:47

2

You can use subquery for this too (assuming your installation table i related to Installations model)

  $subQuery = Installations::find()->select('ref_no')->where("ref_no like '%$q%'");
  $query = Survey::find()->where(['not in', 'ref_no', $subQuery]);
  $models = $query->all();

edited Apr 13 '18 at 09:47

Peerbits - Backend Team

167
9

answered Apr 13 '18 at 07:49

ScaisEdge

131,976
10
91
107

this is a neater approach `+1` as i also use the same **(⌐■_■)** – Muhammad Omer Aslam Apr 13 '18 at 23:30
1

@MuhammadOmerAslam ... thanks. is the approach based on yii2 "guidelines" – ScaisEdge Apr 14 '18 at 05:29

Yasin Patel · Accepted Answer · 2018-04-13T08:57:11.890

1

Change Your Query as Below :

$ser = Survey::find()->where("ref_no like '%$q%'")
->andWhere(['status'=>1])
->andWhere("ref_no NOT IN (select ref_no from installations where ref_no LIKE '%q%')")
->asArray()->all();

OR

$ser = Survey::find()
->where("ref_no like '%$q%' AND ref_no NOT IN (select ref_no from installations where ref_no LIKE '%q%')")
->andWhere(['status'=>1])
->asArray()->all();

edited Apr 13 '18 at 08:57

answered Apr 13 '18 at 07:20

Yasin Patel

5,624
8
31
53

yii2-Active record with not in condition

2 Answers2