1

UPDATE: Solved with the answer below.

$past_events     = array();
$past_events_sql = $wpdb->get_results(
    $wpdb->prepare(
        "SELECT p.ID, from_unixtime(m.meta_value,'%%Y-%%m-%%d') as eventdate, from_unixtime(m.meta_value,'%%H.%%i.%%s') as eventtime, p.post_title, m.meta_value, p.post_date, p.post_status, p.post_type
        FROM wp_posts as p
        JOIN wp_term_relationships as r
            ON p.ID = r.object_id
        JOIN wp_postmeta as m
            ON p.ID = m.post_id
        WHERE r.term_taxonomy_id = %d
        AND m.meta_key = 'event_timestamp'
        AND from_unixtime(m.meta_value) < CURRENT_TIMESTAMP
        ORDER BY eventdate DESC, eventtime ASC;",
        $term->term_taxonomy_id,
        ),
    );

Original question:

We have a listing of events that we would like to be descending by date and within a single date if there are multiple events ascending by time. Originally I tried adding an 'AND' relation and working with orderby as outlined in the codex in the meta_query portion of the WP_Query but that does not work because there are not separate keys for date and time so they need to be calculated from the same event_timestamp.

Since we process the query into objects that include eventDate and eventTime strings, I tried wp_list_sort with the event_timestamp and eventTime as keys to orderby ('DESC' and 'ASC' respectively) but it would only orderby the eventTimestamp. This was the last version of how I was trying that:

$sorted_past_events = wp_list_sort(
   $past_events,
   array(
     'timestampGMT' => 'DESC',
     'eventTime'    => 'ASC',
   ),
);

So now I've circled around to just trying to have the db do all the work and return the events sorted properly. This SQL query works:

global $wpdb;
   
$past_events_query = $wpdb->get_results(
        $wpdb->prepare(
        "SELECT p.ID, from_unixtime(m.meta_value,'%%Y-%%m-%%d') as eventdate, from_unixtime(m.meta_value,'%%H.%%i.%%s') as eventtime, p.post_title, m.meta_value, p.post_date, p.post_status, p.post_type
        FROM wp_posts as p
        JOIN wp_term_relationships as r
            ON p.ID = r.object_id
        JOIN wp_postmeta as m
            ON p.ID = m.post_id
        WHERE r.term_taxonomy_id = %d
        AND m.meta_key = 'event_timestamp'
        ORDER BY eventdate DESC, eventtime ASC;",
        $term-term_taxonomy_id
    ),
    OBJECT,
);

The obstacle now is even when adding the argument to return an OBJECT (which is the default for get_results) it's returning an array. Is there a way to handle this? As I'm fairly new to WordPress, this site has been hugely helpful as I've plodded through this over the past couple of days but at this point I'm spinning my wheels.

Thank you!

3
  • gmdate() expects a timestamp (ie, an integer) as its second (optional) parameter. Your code is passing it a string, which will throw a warning and return false.
    – Pat J
    Jan 7 at 2:33
  • 1
    Thank you, @Pat J! The SQL has been edited to avoid gmdate altogether and now I'm trying to have it return as an object rather than array.
    – Jen Abella
    Jan 7 at 17:54
  • There are a couple typos in your code. There shouldn't be a , after OBJECT (there's only 2 parameters in the function call), and $term-term_taxonomy_id should be $term->term_taxonomy_id (the -> needs to be an arrow).
    – Pat J
    Jan 7 at 19:28

0

Your Answer

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

Browse other questions tagged or ask your own question.