WordPress admin sortable column using two custom fields

1 Views

I’m writing a WordPress plugin to add a sortable column in the admin. The column needs to sort by date, based on two custom fields – one full month name (August, September etc.) and one four-digit year.

I’ve been following this excellent and extremely well commented tutorial but my SQL skills are virtually zero and I don’t know how to join the year field onto the query after the month field. I can make it all work if I use only one field that contains both the month and year, but that’s not how the site is set up.

Read More

How do I join the second field to give me a result in the format %M %Y or %M%Y so that I can convert it to a date?

The relevant part of the code: 

    switch( $orderby ) {

        // If we're ordering by period
        case 'period':

            /* We have to join the postmeta table to include our date in the query. */
            $pieces[ 'join' ] .= " LEFT JOIN $wpdb->postmeta wp_rd ON wp_rd.post_id = {$wpdb->posts}.ID AND wp_rd.meta_key = 'month'";

            // Then tell the query to order by our date
            $pieces[ 'orderby' ] = "STR_TO_DATE( wp_rd.meta_value,'%M %Y' ) $order, " . $pieces[ 'orderby' ];

The full code: 

<?php

    // Register the column
    function month_column_register( $columns ) {

        unset( $columns['tags'], $columns['comments'] ); // Gets rid of these columns

        $new_columns = array();
             foreach( $columns as $key => $value ) {
                $new_columns[ $key ] = $value;
                if ( $key == 'title' )
                   $new_columns[ 'month_column' ] = 'Period'; //Putting the month column after the title column
             }

             return $new_columns;

        return $columns;
    }

    add_filter( 'manage_posts_columns', 'month_column_register' );



    // Display the column content
    function month_column_display( $column_name, $post_id ) {
        if ( 'month_column' != $column_name )
            return;

        $month = get_post_meta($post_id, 'month', true);
        $year = get_post_meta($post_id, 'year', true);

        echo $month . ' ' . $year;
    }

    add_action( 'manage_posts_custom_column', 'month_column_display', 10, 2 );




    function register_sortable ( $columns ) 
    {
        $columns['month_column'] = 'period';
        return $columns;
    }
    add_filter('manage_edit-post_sortable_columns', 'register_sortable');



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

    function manage_wp_posts_be_qe_posts_clauses( $pieces, $query ) {
        global $wpdb;

        /* We only want our code to run in the main WP query AND if an orderby query variable is designated. */
        if ( $query->is_main_query() && ( $orderby = $query->get( 'orderby' ) ) ) {

            // Get the order query variable - ASC or DESC
            $order = strtoupper( $query->get( 'order' ) );

            // Make sure the order setting qualifies. If not, set default as ASC
            if ( ! in_array( $order, array( 'ASC', 'DESC' ) ) )
                $order = 'ASC';

            switch( $orderby ) {

                // If we're ordering by period
                case 'period':

                    /* We have to join the postmeta table to include our date in the query. */
                    $pieces[ 'join' ] .= " LEFT JOIN $wpdb->postmeta wp_rd ON wp_rd.post_id = {$wpdb->posts}.ID AND wp_rd.meta_key = 'month'";

                    // Then tell the query to order by our date
                    $pieces[ 'orderby' ] = "STR_TO_DATE( wp_rd.meta_value,'%M %Y' ) $order, " . $pieces[ 'orderby' ];

                    break;

            }

        }
        return $pieces;
    }

    ?>
Post Views: 1

Related posts