Need to pull out subscription that fallen under condition

I have some issue to resolve the complex nested eloquent whereHas in laravel. I would like to achieve the scenario by using below eloquent query.

The user should only be allowed view the subscription although the subscription has 7 days unpaid invoice and his/her previous invoice must be paid. If the user completed all the unpaid invoice, the subscription should also be displayed

Below are my current model design Subscription

public function invoice(){
        return $this->hasMany(Invoice::class,"subscription_id","id");
}

Invoice

public function subscription(){
        return $this->belongsTo(Subscription::class,"invoice_id","id");
}

Invoice_Status

$s = Subscription::whereHas('invoice',function($q){
        $q->whereHas('status',function($q){
            $q->where(function($q){
                $q->where("status","Unpaid")->whereRaw("DATE_ADD(invoices.created_at,INTERVAL 7 DAY) > CURDATE()");
            })->orWhere(function($q){
                $q->whereHas('invoice.',function($q){
                    $q->latest()->first()->whereHas('status',function($q){
                        $q->where('status','Paid');
                    });
                });
            });
        ->orWhere(function($q){
            $q->latest()->first()->whereHas('status',function($q){
                $q->where('status','Paid');
            });
        });
    })->get();

What I have achieved right now, I can pull those subscription that have unpaid order within 8 day from the date he accessed but if there's no unpaid order, the subscription wont have any data. I no sure which part I have did wrong.

from Newest questions tagged laravel-5 - Stack Overflow https://ift.tt/6RobF8V
via IFTTT