0

I've got a WordPress database with a custom post type called events with custom fields for startdates and enddates of the events. I would like to output all of those events in a list having the upcoming events listed first in an ascending order and the rest listed in a descending order.

I tried to copy the code from this answer but i can't seem to get it right – all the posts are sorted ascendingly: MySQL conditional ORDER BY ASC/DESC for date column

This is my code right now:

SELECT p.post_title, m.meta_value 
FROM wp_posts as p, wp_postmeta as m 
WHERE p.ID = m.post_id 
    AND p.post_type = "events"
    AND m.meta_key = "startdate" 
    AND p.post_status = "publish"
ORDER BY m.meta_value ASC,
    CASE m.meta_value WHEN m.meta_value > DATE(NOW()) THEN m.meta_value END ASC,
    CASE WHEN m.meta_value < DATE(NOW()) THEN m.meta_value END DESC
LIMIT 10
Community
  • 1
  • 1
Richard B
  • 395
  • 1
  • 3
  • 13

2 Answers2

1

Remove the first clause in the order by. It should read:

ORDER BY 
    CASE m.meta_value WHEN m.meta_value > DATE(NOW()) THEN m.meta_value END ASC,
    CASE WHEN m.meta_value < DATE(NOW()) THEN m.meta_value END DESC

Actually, I'm not sure what the collating sequence of NULLs are in postgres. You can get what you want by doing:

ORDER BY 
    CASE m.meta_value WHEN m.meta_value > DATE(NOW()) THEN m.meta_value
                      else cast('9999-01-01' as date) END ASC,
    CASE WHEN m.meta_value < DATE(NOW()) THEN m.meta_value END DESC
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Does this work? I thought CASE required to evaluate to NULL if a value did not match any condition, and that for comparisons mysql would treat the NULLs as a 0... which in the case of dates would mean the past events were sorted to the top, because there are numerically before current dates? – Joel Coehoorn Dec 03 '12 at 15:59
  • I didn't get any of these to work, i only got an ascending list of the events – same as running 'select * from table order by field asc'. Thanks anyway! – Richard B Dec 04 '12 at 09:00
1

I think this will do it. The logic is sound, but I'm not that familiar with mysql and so you may need to do a conversion or two in the 2nd case:

ORDER BY CASE WHEN m.meta_value > DATE(NOW()) THEN 0 ELSE 1 END,
         CASE WEHN m.meta_value > DATE(NOW()) THEN m.meta_value ELSE m.meta_value * -1 END

The trick is that you want to treat dates as a number, and mysql may require you to do this a little differently. It works because within that second level the sort only applies when the values from the first level are equal: so all upcoming events will be sorted to the top, and within those upcoming events there are sorted ascending. All past events will show next, and within that list they will sort descending, because we inverted the date value.

Joel Coehoorn
  • 399,467
  • 113
  • 570
  • 794
  • Almost got this to work! The upcoming events are showing up in the right order but then the previous events are ordered ascendingly. Any idea why? – Richard B Dec 04 '12 at 09:03
  • the m.meta_value * -1 isn't doing what it should. maybe using 1-m.meta_value instead, or look into a cast/convert of some kind. – Joel Coehoorn Dec 04 '12 at 14:11
  • Think I've tried everything here (guess you ment doing kinda like: CAST(m.meta_value AS DATE) etc), but I'm not used to working with conditional statements in SQL so I have practically no clue what I'm doing. Is it possible to do this in another way like using joins somehow? – Richard B Dec 04 '12 at 14:59
  • 1
    try casting to unixtime or an integer. What I'm trying to do in that 2nd case statement is get a negative date value that has the same amplitude, so that when you sort the negative values ascending it's as if we sorted the orginals descending. Do you understand that? – Joel Coehoorn Dec 04 '12 at 15:04
  • Yay, it worked! I just unix_timestamp:ed everything. Thanks! :) – Richard B Dec 04 '12 at 16:16
  • HOw do you do this in SQL Server? – Mike Flynn Jul 31 '13 at 19:47