Laravel 5 Query relationship not working

Question

I have two tables as follows:

1). myurls

    id    domain      
----------------------
    1     google.com
    2     test.com
    3     example.com  etc

2). links

     id  myurls_id   end_date              created_at
---------------------------------------------------
     1     2       2016-11-30 00:00:00   2016-11-30 00:00:00
     2     2       2016-12-01 00:00:00   2016-11-30 00:00:00
     3     2       NULL                  2016-11-30 00:00:00
     4     2       2017-01-01 00:00:00   2016-12-01 00:00:00
     5     3       2016-11-24 00:00:00   2016-12-01 00:00:00
     6     3       2016-10-01 00:00:00   2016-12-01 00:00:00

etc... they have one to many relationship with myurls_id as you can see

in this example for myurls_id (2) we have 4 record : 2 record where end_date is expired 1 record where end_date is NULL 1 record where end_date is NOT expired

I want to write a laravel relationship query where it will get me expired URLs that means query the results where if you find at least one NULL or one not expired not listed we only want to list if all the 4 record are expired. how can I do that

here is what I have but its not working:

$expired_domains = Myurls::with(['links' => function ($query) {
         $query->whereNotNull('end_date')
                ->where('end_date','<',Carbon::now())
                ->where('created_at', '<', Carbon::now())
                ->orderBy('created_at', 'asc')
                ->first();

}])->get();

this is showing me that test.com & example.com both are EXPIRED which is wrong but its not because there is at least one end_date "NULL" or not expired record in links table, it suppose to only give me example.com Expired because all its link end_date expired

How to do this I appreciate your help

Answer 1

To achieve what you are intending, as I understand, can be done as

//In your MyUrls model define two relationships
<?php

namespace App\Models;

use Carbon\Carbon;
use Illuminate\Database\Eloquent\Model;

class MyUrls extends Model
{
    protected $table="myurls";

    protected $fillable = ['domain'];

    public function links()
    {
        return $this->hasMany(Link::class, 'myurls_id');
    }

    public function active_links()
    {
        return $this->hasMany(Link::class, 'myurls_id')
                    ->whereApproved(1)
                    ->where('end_date', '>', Carbon::now());
    }

    public function null_links()
    {
        return $this->hasMany(Link::class, 'myurls_id')
                    ->whereApproved(1)
                    ->whereNull('end_date');
    }

    public function unapproved_active_links()
    {
        return $this->hasMany(Link::class, 'myurls_id')
                    ->whereApproved(0)
                    ->where('end_date', '>', Carbon::now());
    }

    public function unapproved_null_links()
    {
        return $this->hasMany(Link::class, 'myurls_id')
                    ->whereApproved(0)
                    ->whereNull('end_date');
    }
}    

You can apply whatever ordering you need within the relationship for eg return $this->hasMany('active_links', 'myurls_id')->where('end_date', '>', Carbon::now())->orderBy('end_date', 'dsc');.

If you are using default timestamps() in your migrations, then you probably do not need to check where('created_at', '<', Carbon::now());.

Then in your controller (or wherever else) you can get the domains data as

$allDomains = MyUrls::all();  //list of all domains

//All domains which have links but do not have active_links are expired.
//Domains without any links will not be listed here.
$expiredDomains = MyUrls::has('links')
                         ->doesntHave('active_links')
                         ->doesntHave('null_links')
                         ->doesntHave('unapproved_active_links')
                         ->doesntHave('unapproved_null_links')
                         ->get();

//All domains which have active_links are active.
//Domains without any links will not be listed here.
$activeDomains = MyUrls::has('active_links')->get();  

Then in your view you can use

    <h4>All Domains</h4>
    <ul>
        @foreach($allDomains as $domain)
            <li>{{$domain->domain}}</li>
        @endforeach
    </ul>
    <hr />
    <h4>Expired Domains</h4>
    <ul>
        @foreach($expiredDomains as $domain)
            <li>{{$domain->domain .' is an expired domain '}}</li>
        @endforeach
    </ul>
    <hr />
    <h4>Active Domains</h4>
    <ul>
        @foreach($activeDomains as $domain)
            <li>{{$domain->domain .' has an end date of '.$domain->active_links[0]->end_date}}</li>
        @endforeach
    </ul>  

Hope this is what you are intending and it helps get you started.