Laravel Mysql Query Select with condition

I have two tables as follows:

1). urls

    id    domain             created_at
------------------------------------------------
    1     google.com           2016-11-30 00:00:00
    2     test.com             2016-11-29 00:00:00
    3     example.com      2016-11-26 00:00:00

etc.. 2). links

     id  urls_id   end_date              created_at            status
---------------------------------------------------------------------------
     1     2       2016-11-30 00:00:00   2016-11-30 00:00:00     Approved
     2     2       2016-12-01 00:00:00   2016-11-30 00:00:00     Approved
     3     2       NULL                  2016-11-30 00:00:00     Approved
     4     2       2017-01-01 00:00:00   2016-12-01 00:00:00     Approved
     5     2       2016-01-01 00:00:00   2016-12-01 00:00:00     Pending
     6     3       2016-11-24 00:00:00   2016-12-01 00:00:00     Pending
     7     3       2016-01-01 00:00:00   2016-12-01 00:00:00     Approved
     8     3       NUll                  2016-12-02 00:00:00     Pending
     9     3       2016-01-08 00:00:00   2016-12-01 00:00:00     Approved
     10    3       NUll                  2016-12-02 00:00:00     Pending

I would like to write a MYSQL Query to return with these conditions: one row per domain that follows these conditions: 1). end_date NOT NULL AND 2). end_date > Carbon::now() (I am using carbon) not expired AND 3). status is "Approved" AND 4). return latest end_date closer to present or now.

so for example, it will look and find all "urls_id"= 2 and select ALL status Approved and look for end_date that is NOT NULL and not expired , but since there is one Null then it wont return it, then move on to "urls_id"= 3 and we have 2 approved and both expired and there is no end_date NULL or not expired so we only will return that row of the latest end_date

here is an output

  id     domain          end_date_max        
  ------------------------------
  9      example.com     2016-01-08 00:00:00 

how we can do this in one SQL query ? in Laravel or just plain Raw query

Thanks

via Chebli Mohamed