Yii2 Query Builder with ActiveRecord

Question

I have three models Employer , Job, and Transaction

Employer can have many Job

Job can have many Transaction

I am trying to use ActiveRecord to get all Employer that do not have a Transaction record.

Within my Employer model, I have defined relations to find all jobs and transactions linked to this employer:

/**
 * @return \yii\db\ActiveQuery
 */
public function getJobs() {
    return $this->hasMany(Job::className(), ['employer_id' => 'employer_id']);
}

/**
 * @return \yii\db\ActiveQuery
 */
public function getTransactions() {
    return $this->hasMany(Transaction::className(), ['job_id' => 'job_id'])->via("jobs");
}

Any ideas on the best way to do this?

Khalid Al-Mutawa · Accepted Answer · 2015-05-21 12:00:20Z

3

SQL:

SELECT employer.* 
FROM   employer 
WHERE  employer.employer_id NOT IN 
(
  SELECT employer.employer_id 
  FROM employer 
    INNER JOIN job         ON employer.employer_id = job.employer_id
    INNER JOIN transaction ON           job.job_id = transaction.job_id
)

With Yii2:

function getThoseWithoutTransaction() {
    return Employer::find()->where(['not in', 'employer_id', 
        (new Query())
            ->select('employer.employer_id')
            ->from('employer')
            ->innerJoin('job', 'employer.employer_id = job.employer_id')
            ->innerJoin('transaction', 'job.job_id = transaction.job_id')
        )]
    );
}

But I didn't test it. Hope it is correct, though. And there might be better solutions.

edited May 21, 2015 at 12:00

Khalid Al-Mutawa

8791 gold badge9 silver badges17 bronze badges

answered May 21, 2015 at 11:26

robsch

9,76810 gold badges69 silver badges107 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

ScaisEdge · Accepted Answer · 2015-05-21 10:17:23Z

1

Try with

$query = MyModel::find()->where(['not in','attribute',$array]);

answered May 21, 2015 at 10:17

ScaisEdge

134k10 gold badges98 silver badges111 bronze badges

2 Comments

Khalid Al-Mutawa Over a year ago

This doesn't work. I'm looking to get all Employers which don't have transactions (through job)

ScaisEdge Over a year ago

If in the $array you have the distinct Employers which have transation and in attribute the employer_id this is the righit solution

Collectives™ on Stack Overflow

Yii2 Query Builder with ActiveRecord

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related