1

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

  • 1
    (links.status = "Approved" and links.end_date is not null) seems to be redundant, and can be removed. – Rick James Dec 9 '16 at 6:31
  • Are the duplicate domains in urls? If so, which created_at do you want to get after the GROUP BY? – Rick James Dec 9 '16 at 6:32
0
SELECT 
    u1.id,
    u1.domain,
    t1.end_date

FROM urls u1
INNER JOIN 
        (SELECT 
            l1.urls_id,
            MIN(end_date) as end_date       
         FROM urls u2 
         INNER JOIN links l1 
             ON u2.id = l1.urls_id 
            AND l1.status = "Approved" 
            AND (l1.end_date > now() OR l1.end_date IS NOT NULL)
         GROUP BY l1.urls_id) t1
ON u1.id = t1.urls_id

Source requirements (not 100% sure about p4, so this could be changed):

1). end_date NOT NULL  AND
2). end_date > now()   
3). status is "Approved" AND
4). return latest end_date closer to present or now.
  • l1.end_date > now() OR l1.end_date IS NOT NULL if and only if l1.end_date IS NOT NULL – Lennart May 31 '18 at 4:53
  • @Lennart - after 2 years it is hard to remember full logic. will need re-check - why it was. looking with fresh eyes - just need leave l1.end_date > now() – a_vlad May 31 '18 at 5:57
  • Ah, I did not realize the answer was that old. The question popped up so I wrongly assumed it was a new one. – Lennart May 31 '18 at 6:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.