Support

Account

Register Login

Home › Forums › General Issues › meta_query orderby overwritten by publish date

Solved

meta_query orderby overwritten by publish date

  • Strange problem I’m having. It seems my WP_Query is grouping my posts by publish date and then ordering everything in each group by the meta_key I’ve specified. Here is my query:

    Here is my meta_query using WP_Query:

$schedule_query = new WP_Query(array(
        'posts_per_page' => -1,
        'cat' => '4,5,33',
        'meta_query' => array(
            'relation' => 'OR',
            array(
            'key' => 'date',
            'value' => $day,
            'compare' => 'LIKE'
            ),
            array(
                'key' => 'day',
                'value' => $day,
                'compare' => 'LIKE'
            )
        ),
        'meta_key' => 'schedule_time_start',
        'orderby' => 'meta_value_num',
        'order' => 'ASC'
    ));

    How do I modify this so meta_value_num is used to order all of the returned posts?

  • Your query will order everything by schedule_time_start, what is that field and what’s in it? Basically, it will be ordered by this value, when this value is the same in 2 posts then the order will be determined by the post ID which means the order they were created.

  • Hi John,

    Thanks for your reply.

    schedule_time_start is a “Date and Time Picker” field. It is ordering everything correctly by this field BUT only for posts published/updated on the same date. It is as if they are being grouped by the standard WordPress publish date and then sorted by schedule_time_start within those groups.

    My questions is: why is this happening and how can I modify my query to return all posts into one group, instead of multiple?

    Thanks!

  • *bump*

    Any thoughts from the community would be appreciated!

  • Sorry, for some reason I did not see you’re previous reply. I don’t know much about the date and time picker, how does this field type store values in the database? Knowing how the values are stored will will help me figure out why it’s doing what it’s doing.

  • Hi John, Thanks for replying. The value is stored as a Unix timestamp (seconds since the Unix epoch). So 9am GMT on August 6, 2016 is 1459242000.

    I still don’t understand why the WordPress publish date is factoring into this and affecting the sort. Any thoughts?

  • if schedule_time_start stores a unix time stamp, then there isn’t any reason for the post date to have anything to do with the sort order, so I’m completely confused.

    I would probably add a filter on the query and log them to see what is actually being run

    
add_filter('query', 'log_query');
// do your query
remove_filter('query', 'log_query');

function log_query($query) {
  error_log($query);
  return $query;
}

    This will put the query into the error log. You may need to turn on error logging in WP, but I’m not sure about that.

  • Hi John,

    I wasn’t able to get that filter working to log the queries in the error log, but I did do a print_r on the query array and got the MySQL query:

    SELECT   wp_posts.* FROM wp_posts  INNER JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id) INNER JOIN wp_postmeta ON ( wp_posts.ID = wp_postmeta.post_id )  INNER JOIN wp_postmeta AS mt1 ON ( wp_posts.ID = mt1.post_id ) WHERE 1=1  AND ( 
  wp_term_relationships.term_taxonomy_id IN (4,5,33,34,35,41)
) AND ( 
  wp_postmeta.meta_key = 'schedule_time_start' 
  AND 
  ( 
    ( mt1.meta_key = 'date' AND CAST(mt1.meta_value AS CHAR) LIKE '%1470268800%' ) 
    OR 
    ( mt1.meta_key = 'day' AND CAST(mt1.meta_value AS CHAR) LIKE '%1470268800%' )
  )
) AND wp_posts.post_type = 'post' AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'acf-disabled' OR wp_posts.post_status = 'private') GROUP BY wp_posts.ID ORDER BY wp_postmeta.meta_value+0 ASC

    See anything unusual?

  • Nevermind. I figured out the problem. Nothing to do with the query, but with the unix timestamps being saved to the database — the values were wrong, and that was causing the issue. Thanks for your efforts, John.

  • Glad you got it worked out

  • Hi vanderduke, how were you able to fix that? I am having the same issue.
    I am trying to sort posts Ascending based on a Date and Time Picker custom field called: date_and_time_picker. The code I have is the following:

    $paged = (get_query_var(‘paged’)) ? get_query_var(‘paged’) : 1;
    query_posts($query_string.’&category_name=events&meta_key=date_and_time_picker&meta_compare=>=&meta_value=’.strtotime(date(“Y-m-d”)).’&orderby_meta_key=date_and_time_picker&order=ASC&posts_per_page=12&paged=’ . $paged);

    For some reason it is sorting using the post date instead. Do you know what could be wrong with this code?
    This is the page: http://www.beckywasserman.com/current-events/

    Thanks,
    Andrea

  • Hi Andrea,

    So, the problem I had was that I made the mistake of thinking that my date and time picker custom field (showing time only) was linked/relative to the date and time picker custom field (displaying both date and time). They’re not linked, rather the time only picker value sets the time relative to the publish date of the post!

    So, here’s how my fields were set up:

    Date of event: date and time picker field (field name: day)

    Event time start: date and time picker field — time display only (field name: schedule_time_start)

    Event time finish: date and time picker field — time display only (field name: schedule_time_finish)

    So, for example, a post using these fields would be entered as:

    Date of event: 8/6/2016 12:00am
    Event time start: 9:00am
    Event time finish: 10:00am

    If I published this post on say, April 1, 2016 the ‘schedule_time_start’ was stored as 1459501200 which is the equivalent of April 1, 2016 9:00am GMT!

    That was the problem. What I should have done was ditched the event date field and just used the event time start & finish fields as date and time pickers.

    I had to debug this by querying the values that were being store in the wp_postmeta table in the database. This showed that the time values were set relative to the publish date.

    I hope this helps you.

  • Great! Thanks 🙂

Viewing 13 posts - 1 through 13 (of 13 total)

The topic ‘meta_query orderby overwritten by publish date’ is closed to new replies.