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 won't 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
Here is what I have tried but it's selecting all domains (using Where not exists)
select * from urls
where not exists (
select *
from links
where urls.id = links.urls_id
and (links.status = "Approved"
and links.end_date is not null)
and (links.status = "Approved"
and links.end_date > 2016-12-08 23:46:16)
)
group by urls.domain
How we can do this in one SQL query? In Laravel or just plain Raw query.
Thanks
Best Answer
Source requirements (not 100% sure about p4, so this could be changed):