mardi 19 février 2019

How to get the row with two or more specific condition?

ApplicantTbl

id | name          |
--------------------
 1 | John Doe      |
 2 | Maria Ozawa   |
 3 | Catriona Gray |
--------------------

EnrollmentRequestContentTbl

id |  applicant_id | payment_status  |   enrollment_status   |
--------------------------------------------------------------
 1 |       1       |     pending     |        null           |
--------------------------------------------------------------

My goal is to get all the applicants including the applicant_id 1 if:

  1. The payment_status on enrollmentRequestContentTbl is paid
  2. The payment_status on enrollmentRequestContentTbl is unpaid and the enrollment_status is back_out or declined

The rest posible condition will be ignored and will not be able to get from my query.

The above table will ignore the applicant 1 since the payment_status is pending.

If the EnrollmentRequestContentTbl will look like this

 id |  applicant_id | payment_status  |   enrollment_status   |
 --------------------------------------------------------------
 1 |       1        |     paid        |        null           |
 --------------------------------------------------------------

or this. Since the paymet_status is unpaid enrollment_status must be back_out or declined

 id |  applicant_id | payment_status  |   enrollment_status   |
 --------------------------------------------------------------
 1 |       1        |     unpaid      |        back_out       |
 --------------------------------------------------------------

-

 id |  applicant_id | payment_status  |   enrollment_status   |
 --------------------------------------------------------------
 1 |       1        |     unpaid      |        declined       |
 --------------------------------------------------------------

The applicant 1 will be included to my query and show on my list.

-

ApplicantModel

public function enrollmentRequestContent()
{
   return $this->hasMany('App\EnrollmentRequestContent');
}

EnrollmentRequestContentModel

public function applicant()
{
   return $this->belongsTo('App\Applicant')
}

So far this is what I've tried.

Controller

public function getApplicant()
{
    $applicants = Applicant::orWhereDoesntHave('enrollmentRequestContent', function($q){
        $q->where('payment_status', '!=', 'paid')
            ->where(function($qq) {
                $qq->where('payment_status', '!=', 'unpaid')
                        ->whereIn('enrollment_status', ['declined', 'back_out']);
            });
    })->get();   

    return $applicants;
}

Im using laravel eloquent query.



via Chebli Mohamed

Aucun commentaire:

Enregistrer un commentaire