How to add a sortable date column in an admin page?

Question

How to add a sortable date column in an admin page?

Sasha Grievus

2022年2月21日 20:06

I'm trying to add a date column to an admin table (to woocommerce order page, just to be specific, but this code is not plugin dependent). The code works for adding the column, but whenever I try to sort the column tapping the sorting triangle button on the order page it returns 'No record found'. Maybe there is a problem with date format in the function returning the date? The rest of the code is pretty standard

// Adding a new column to ADMIN order list
add_filter( 'manage_edit-shop_order_columns', 'custom_shop_order_column', 20 );
function custom_shop_order_column($columns)
{
    $reordered_columns = array();

    // Inserting columns to a specific location
    foreach( $columns as $key = $column){
        $reordered_columns[$key] = $column;
        if( $key ==  'order_status' ){
            // Inserting after Status column
            $reordered_columns['closing_date_column'] = __( 'Validación','theme_domain');
        }
    }
    return $reordered_columns;
}

// Adding custom fields meta data for new column in ADMIN order list
// to add closing date to order solicitado passed to en espera (being so closed)
add_action( 'manage_shop_order_posts_custom_column' , 'custom_orders_list_column_content', 20, 2 );
function custom_orders_list_column_content( $column, $post_id )
{
    switch ( $column )
    {
        case 'closing_date_column' :
            echo calculateClosingDate($post_id);
            break;
    }
}

// Make custom column sortable
add_filter( manage_edit-shop_order_sortable_columns, 'shop_order_column_meta_field_sortable' );
function shop_order_column_meta_field_sortable( $columns )
{
    $meta_key = 'closing_date_column';
    return wp_parse_args( array('closing_date_column' = $meta_key), $columns );
}

// Make sorting work properly 
add_action('pre_get_posts', 'shop_order_column_meta_field_sortable_orderby' );
function shop_order_column_meta_field_sortable_orderby( $query ) {
    global $pagenow;

    $orderby  = $query-get( 'orderby');
    if ('closing_date_column' === $orderby){
        $query-set('meta_key','closing_date_column');
        $query-set('orderby', 'meta_value_date');
    }    
}
// Adding a new column to ADMIN order list
function calculateClosingDate($post_id){
    // doing stuff
}

EDIT: I think the problem may be that the data shown in the custom table is calculated and not stored in a metakey value on the db, so whenever it tries to sort is searching on the db for not existing data. So How I can sort with the calculated data?

Topic table date Wordpress

Category Web

Sasha Grievus · Accepted Answer · 2022年2月21日 20:06

Just for people passing by, for putting together all this code was not trivial. The problem was actually the lack of data. I solved adding data as metakey (added in my case when the order changes status) and echoing it as the column value. So the full code is:

// Adding a new column to ADMIN order list
add_filter( 'manage_edit-shop_order_columns', 'custom_shop_order_column', 20 );
function custom_shop_order_column($columns)
{
    $reordered_columns = array();

    // Inserting columns to a specific location
    foreach( $columns as $key => $column){
        $reordered_columns[$key] = $column;
        if( $key ==  'order_status' ){
            // Inserting after "Status" column
            $reordered_columns['closing_date_column'] = __( 'Validación','theme_domain');
        }
    }
    return $reordered_columns;
}

// Echoing custom fields meta data for new column in ADMIN order list
add_action( 'manage_shop_order_posts_custom_column' , 'custom_orders_list_column_content', 20, 2 );
function custom_orders_list_column_content( $column, $post_id )
{
    switch ( $column )
    {
        case 'closing_date_column' :
            echo get_post_meta( $post_id, 'closing_date_column', true );
            break;
    }
}

// Make custom column sortable
add_filter( "manage_edit-shop_order_sortable_columns", 'shop_order_column_meta_field_sortable' );
function shop_order_column_meta_field_sortable( $columns )
{
    $meta_key = 'closing_date_column';
    return wp_parse_args( array('closing_date_column' => $meta_key), $columns );
}

// Make sorting work properly for date field
add_action('pre_get_posts', 'shop_order_column_meta_field_sortable_orderby' );
function shop_order_column_meta_field_sortable_orderby( $query ) {
    global $pagenow;

    $orderby  = $query->get( 'orderby');
    if ('closing_date_column' === $orderby){
        $query->set('meta_key','closing_date_column');
        $query->set('orderby', 'meta_value_date');
    }    
}

// add closing date to order meta when it changes status
// The format is "Y-m-d" for sorting doesn't work with any date format
add_action( 'woocommerce_order_status_changed', 'action_woocommerce_order_status_changed', 10, 4 ); 
function action_woocommerce_order_status_changed( $this_get_id, $this_status_transition_from, $this_status_transition_to, $instance ) { 
    update_post_meta( $this_get_id, 'closing_date_column', date("Y-m-d") );
};

How to add a sortable date column in an admin page?

About