Wordpress自定义帖子类型列排序查询

Question

I have a custom post type called reservation. In it I get the reservation for event/room you name it (not important).

When I go to my custom post type page, I get all the posts nicely displayed. The first column is the reservation date. Now the reservation date can be one - 28.11.2015, but it can also be multiple dates - 28.11.2015, 29.11.2015, 02.12.2015.

I would like to enable the sorting of that column. The problem is, this is a string, not a number so I cannot just create a simple sortable column query like described here.

So far I tried this:

add_filter( 'manage_edit-reservation_sortable_columns', 'reservation_sortable_column' );

if (!function_exists('reservation_sortable_column')) {
    function reservation_sortable_column( $columns ) {
        $columns['reservation_date'] = 'reservation_date';
        return $columns;
    }
}

add_filter( 'posts_clauses', 'manage_wp_posts_be_qe_pre_get_posts', 1, 2 );

if (!function_exists('manage_wp_posts_be_qe_pre_get_posts')) {
    function manage_wp_posts_be_qe_pre_get_posts( $pieces, $query ) {
        global $wpdb;
        if ($query->is_main_query() && ( $orderby = $query->get('orderby') ) ) {
            $order = strtoupper( $query->get('order') );
            if ( !in_array( $order, array('ASC', 'DESC') ) ) {
                $order = 'ASC';
                switch ($orderby) {
                    case 'reservation_date':
                        $pieces[ 'join' ] .= " LEFT JOIN $wpdb->postmeta wp_rd ON wp_rd.post_id = {$wpdb->posts}.ID AND wp_rd.meta_key = 'reservation_date'";
                        $pieces[ 'orderby' ] = "STR_TO_DATE( wp_rd.meta_value,'%d.%m.%Y' ) $order, " . $pieces[ 'orderby' ];
                        break;
                }
            }
            return $pieces;
        }
    }
}

This doesn't work, because I'm working with strings.

Luckily, my dates (when a person picks them), are already sorted lowest to highest date. They are stored in $custom['reservation_date'] array, where $custom = get_post_custom($post->ID). So I can get them like $custom['reservation_date'][0].

I can also separate the first date

preg_match('/^(.+?),/', $custom['reservation_date'][0], $matches);

If it's only one date, without the comma, I can then get them out like:

$first_date = (!empty($matches)) ? $matches[0] : $custom['reservation_date'][0];

Now what's bothering me is how to use this in my custom query? Currently the sorting is done on meta_value reservation_date (string). How do I make it so that I can sort based on these first dates?

Say I have dates like:

19.11.2015, 20.11.2015

28.11.2015

14.11.2015, 15.11.2015

13.11.2015

When I click to sort them in ascending order I'd like to get

13.11.2015

14.11.2015, 15.11.2015

19.11.2015, 20.11.2015

28.11.2015

And another click on the column will order them descending

28.11.2015

19.11.2015, 20.11.2015

14.11.2015, 15.11.2015

13.11.2015

Any help is appreciated.

Answer 1

You will not be able to sort in the WP Query as you have not stored the values in a way WordPress understands.

https://codex.wordpress.org/Class_Reference/WP_Query#Custom_Field_Parameters

You should store dates as YYYY-mm-dd.

Either change how the dates are stored or use PHP after the database request to sort.

// 1. if you change how "reservation_date" is stored in the db

$posts = get_posts(array(
    'posts_per_page' => -1,
    'post_type' => 'reservation',
    'meta_key' => 'reservation_date',
    'orderby' => 'meta_value',
    'order' => 'ASC'
));

// 2. if you choose to sort with PHP after pulling the data

$posts = get_posts(array(
    'posts_per_page' => -1,
    'post_type' => 'reservation'
));

usort($posts, function ($post_a, $post_b) {
    $post_a_custom = get_post_custom($post_a->ID);
    $post_b_custom = get_post_custom($post_b->ID);

    $post_a_date = DateTime::createFromFormat('d.m.Y', $post_a_custom['reservation_date'][0]);
    $post_b_date = DateTime::createFromFormat('d.m.Y', $post_b_custom['reservation_date'][0]);

    if ($post_a_date == $post_b_date) {
        return 0;
    } else if ($post_a_date > $post_b_date) {
        return -1;
    } else {
        return 1;
    }
});