WooCommerce Custom Product Column Sortable Backed not working

Question

I have a custom postmeta field which stores the post/product_id in a serialized way. Example _related_ids => a:4:{i:0;i:2462;i:1;i:2466;i:2;i:2469;i:3;i:2472;}

I'm showing the product_id count of _related_ids in WooCommerce Product Listing screen (Backed), which is working fine. Now I want to make that column sortable. So I write a function related_product_col_sort which is hooked into manage_edit-product_sortable_columns. Column Shorting is not working. (not working means it is not ordering the product_id count properly).

Here is my full code

//_related_ids => a:4:{i:0;i:2462;i:1;i:2466;i:2;i:2469;i:3;i:2472;}
//manage_product_posts_custom_column
add_filter('manage_edit-product_columns', 'related_product_col');

function related_product_col($columns) {
    $new_columns = (is_array($columns)) ? $columns : array();
    $new_columns['RELATED'] = 'Related Product';
    return $new_columns;
}

add_action('manage_product_posts_custom_column', 'related_product_col_data', 2);

function related_product_col_data($column) {
    global $post;
    $related_product_ids = get_post_meta($post->ID, '_related_ids', true);
    if ($column == 'RELATED') {
        if (isset($related_product_ids) && !empty($related_product_ids)) {
            echo count($related_product_ids);
        } else {
            echo "--";
        }
    }
}

add_filter("manage_edit-product_sortable_columns", 'related_product_col_sort');

function related_product_col_sort($columns) {
    $custom = array(
        'RELATED' => '_related_ids'
    );
    return wp_parse_args($custom, $columns);
}

Can anyone help me out with the correct logic/code with related_product_col_sort function.

Thanks.

Answer 1

Long Answer short - Basically, if you need to sort on a meta_value you can't store it serialized. Check https://wordpress.stackexchange.com/questions/87265/order-by-meta-value-serialized-array.

I think the best possible solution for you is to store the count of related products in a new meta_key and use that to sort the columns.

Below are the steps to sort your data if the column consists normal data instead of a serialized array.

There are actually 2 steps to make a custom column sortable

1. Register the column as sortable
2. Implement the sort logic

To register a column as sortable you use the manage_{YOUR_SCREEN_ID}_sortable_columns filter and add your column

You have already registered your column using the related_product_col_sort function, to implement the sort functionality you have couple ways depending on the type of data.

If the data is numeric or simple alphabets you can use the pre_get_posts action and set the meta_key and orderby values

add_action( 'pre_get_posts', 'manage_wp_posts_my_custom_pre_get_posts', 1 );
function manage_wp_posts_my_custom_pre_get_posts( $query ) {

   /**
    * 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' ) ) ) {

      switch( $orderby ) {

         // If we're ordering by 'film_rating'
         case 'RELATED':

            // set our query's meta_key, which is used for custom fields
            $query->set( 'meta_key', 'RELATED' );

            /**
             * Tell the query to order by our custom field/meta_key's
             * This will only work is the meta_value for the RELATED key is either a number or a simple string
             */
            $query->set( 'orderby', 'meta_value' );

            break;

      }

   }
}

If your sorting value is more complicated like a date

add_filter( 'posts_clauses', 'manage_wp_posts_my_custom_posts_clauses', 1, 2 );
function manage_wp_posts_my_custom_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 release_date
         case 'RELATED':

            /**
             * We have to join the postmeta table to
             * include our release 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 = '_related_ids'";

            // This will only work if the meta_value is a date.
            $pieces[ 'orderby' ] = "STR_TO_DATE( wp_rd.meta_value,'%m/%d/%Y' ) $order, " . $pieces[ 'orderby' ];

         break;

      }

   }

   return $pieces;

}