0

I have a custom post type for events with a custom field named event_start_date, and I want to sort the archive loop according to the nearest event_start_date. In other words, the event with the nearest event_start_date to today should be displayed first.

The SQL query for this would be ORDER BY DATEDIFF(event_start_date, NOW()) ASC. But I don't really know how to get this to work in wordpress.

I'm a completely new WP developer, and I've already tried playing around with the args for WP_Query. It only allows me to order by event_start_date which sorts the events as earliest date first, which is not the same as nearest date first.

So is it possible to order a custom field of type DATE according to the difference in date/time from today? If it is how can I do it?

  • You can't do it with WP_Query arguments, but there are filters that let you modify the SQL directly, like posts_orderby – Milo Jan 29 '16 at 5:52
  • Thanks @Milo that looks like what I want. I'll have a look at it. – ThimalW Jan 29 '16 at 5:59
0

You can handle this by define DATEDIFF() in SELECT For example:

SELECT RECID, DATEDIFF(t1.qr_enddate, NOW()) AS diffORD 
FROM `mytablw` t1 
ORDER BY diffORD ASC;

This should work but in your case I strongly recommend to use wordpress default Filter API posts orderby. This filter is applied before a post-retrieving SQL statement is executed. Use it to make custom modifications to the orderby.

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.