mercredi 7 décembre 2016

Laravel 5 Query relationship not working

I have two tables as follows:

1). myurls

    id    domain      
----------------------
    1     www.google.com
    2     wwww.test.com
    3     www.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

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 is EXPIRED but its not because there is at least one end_date "NULL" or not expired record in links table

How to do this I appreciate your help



via Chebli Mohamed

Aucun commentaire:

Enregistrer un commentaire