dingo_d dingo_d - 5 months ago 29
PHP Question

Sorting custom taxonomy column saved as serialized array

I was trying to sort my custom made column in taxonomy following the usual approach, but then I noticed that the value is stored in my options table as a serialized array and that... complicated things.

I created a

knowledgebase
custom post type with
knowledgebase_category
taxonomy.

I added category order field using
knowledgebase_category_edit_form_fields
and
knowledgebase_category_add_form_fields
hooks and saved them using

add_action ( 'edited_knowledgebase_category', 'mytheme_save_extra_knowledgebase_category_fileds');
add_action ( 'created_knowledgebase_category', 'mytheme_save_extra_knowledgebase_category_fileds');

if ( ! function_exists( 'mytheme_save_extra_knowledgebase_category_fileds' ) ){
function mytheme_save_extra_knowledgebase_category_fileds( $term_id ) {
if ( isset( $_POST['Cat_meta'] ) ) {
$t_id = $term_id;
$cat_meta = get_option( "category_$t_id");
$cat_keys = array_keys($_POST['Cat_meta']);
foreach ($cat_keys as $key){
if(isset($_POST['Cat_meta'][$key])){
$cat_meta[$key] = $_POST['Cat_meta'][$key];
}
}
update_option( "category_$t_id", $cat_meta );
}
}
}


This stores the value as a serialized array. The values in
options
table look like this

a:1:{s:9:"cat_order";s:1:"3";}
a:1:{s:9:"cat_order";s:2:"34";}
a:1:{s:9:"cat_order";s:1:"8";}
a:1:{s:9:"cat_order";s:2:"21";}
a:1:{s:9:"cat_order";s:2:"67";}
a:1:{s:9:"cat_order";s:1:"6";}


So sorting is tricky.

I made my column and made it sortable

add_filter('manage_edit-knowledgebase_category_columns', 'mytheme_cat_order_column', 10, 2);

if (!function_exists('mytheme_cat_order_column')) {
function mytheme_cat_order_column( $cat_columns ){
$cat_columns['cat_order'] = esc_attr__('Category Order', 'mytheme');
return $cat_columns;
}
}


add_filter ('manage_knowledgebase_category_custom_column', 'mytheme_manage_knowledgebase_category_custom_fields', 10,3);

if (!function_exists('mytheme_manage_knowledgebase_category_custom_fields')) {
function mytheme_manage_knowledgebase_category_custom_fields($deprecated, $column_name, $term_id){
if ($column_name == 'cat_order') {
$cat_meta = get_option( "category_$term_id");
if (isset($cat_meta['cat_order']) && $cat_meta['cat_order'] != '') {
echo intval($cat_meta['cat_order']);
}
}
}
}


add_filter('manage_edit-knowledgebase_category_sortable_columns', 'mytheme_manage_knowledgebase_category_sortable_columns');

if(!function_exists('mytheme_manage_knowledgebase_category_sortable_columns')){
function mytheme_manage_knowledgebase_category_sortable_columns($columns){
$columns['cat_order'] = esc_attr__('Category Order', 'mytheme');
return $columns;
}
}


And I tried to sort it using this

add_filter( 'terms_clauses', 'mytheme_cat_order_orderby', 10, 3 );

if (!function_exists('mytheme_cat_order_orderby')) {
function mytheme_cat_order_orderby( $pieces, $taxonomies, $args ) {

$orderby = isset($_REQUEST['orderby']) ? trim(wp_unslash($_REQUEST['orderby'])) : 'Category Order';
$order = isset($_REQUEST['order']) ? trim(wp_unslash($_REQUEST['order'])) : 'DESC';
$trm = '"';

if($orderby == 'Category Order' ) {
$pieces['join'] .= " INNER JOIN wp_options AS opt ON opt.option_name = concat('category_',t.term_id)";
$pieces['orderby'] = "ORDER BY CAST( ( SUBSTRING_INDEX( SUBSTRING_INDEX( opt.option_value,';',2 ),':',-1 ) ) AS UNSIGNED)";
$pieces['order'] = $order;
}

return $pieces;

}
}


Now I tried to see what the query should return without casting the

SUBSTRING_INDEX( SUBSTRING_INDEX( opt.option_value,';',2 ),':',-1 )


part, but I got an error and that was probably because I had
""
around my number (a conclusion I got to by using this question as a reference and just testing it in php). So I used
CAST
to try to turn the value to integer.

Now when I click on the column to sort it nothing happens.

Is it possible to sort this mess (serialized array)?

Answer

I realized that since release of WordPress 4.4 there is a term meta. So I changed my code to updates and sets the additional field in the term meta like this:

add_action ( 'created_knowledgebase_category', 'mytheme_save_extra_knowledgebase_category_fileds');

if ( ! function_exists( 'mytheme_save_extra_knowledgebase_category_fileds' ) ){
    function mytheme_save_extra_knowledgebase_category_fileds( $term_id ) {
        if ( isset( $_POST['cat_order'] ) ) {
            $cat_order = intval($_POST['cat_order']);
            add_term_meta( $term_id, 'cat_order', $cat_order, true );
        }
    }
}

Similarly I modified the edited_knowledgebase_category hook. Also the function to add term meta looks like this:

add_action ( 'knowledgebase_category_edit_form_fields', 'mytheme_extra_knowledgebase_category_fields');

if ( ! function_exists( 'mytheme_extra_knowledgebase_category_fields' ) ){
    function mytheme_extra_knowledgebase_category_fields( $term ) {
        $cat_order = get_term_meta( $term->term_id, 'cat_order', true );
        ?>
        <tr class="form-field">
            <th scope="row" valign="top"><label for="cat_order"><?php esc_attr_e('Category order', 'mytheme'); ?></label></th>
            <td><input type="text" name="cat_order" for="cat_order" id="cat_order" value="<?php echo intval($cat_order); ?>"></td>
        </tr>
    <?php
    }
}

Because I'm pulling info from the termmeta table. When you're creating the new field, just leave the input empty (because when you're creating it it should be empty - this is different in the case of selects - look here for more info about that).

Now since I have clear data on which to sort by I updated my sortable column, so that the name by which it sorts is just cat_order (for the simplicity sake)

add_filter('manage_edit-knowledgebase_category_sortable_columns', 'mytheme_manage_knowledgebase_category_sortable_columns');

if(!function_exists('mytheme_manage_knowledgebase_category_sortable_columns')){
    function mytheme_manage_knowledgebase_category_sortable_columns($sortable){
        $sortable['cat_order'] = 'cat_order';
        return $sortable;
    }
}

And sorting is really easy then:

/**
 * Custom column sortable query
 *
 * @param array $pieces     Terms query SQL clauses.
 * @param array $taxonomies An array of taxonomies.
 * @param array $args       An array of terms query arguments.
 * @return string           Modified query
 * @since   1.0.0
 */

add_filter( 'terms_clauses', 'mytheme_filter_custom_terms', 10, 3 );

if (!function_exists('mytheme_filter_custom_terms')) {
    function mytheme_filter_custom_terms( $pieces, $taxonomies, $args ) {

        global $wpdb;

        $orderby = isset($_REQUEST['orderby']) ? trim(wp_unslash($_REQUEST['orderby'])) : 'cat_order';
        $order   = isset($_REQUEST['order'])   ? trim(wp_unslash($_REQUEST['order']))   : 'DESC';

        if($orderby == 'cat_order' ) {
            $pieces['fields']   .= ", tm.*";
            $pieces['join']   .= " INNER JOIN {$wpdb->termmeta} AS tm ON tt.term_id = tm.term_id";
            $pieces['orderby'] = "ORDER BY ABS(tm.meta_value)";
        }

        return $pieces ;

    }
}

First you check if you're ordering by the new column you've created, then you need to add new field in the SELECT part of your query in $pieces['fields'] array, then join the termmeta table with the correct term_id and then order by the meta_value.

Since I'm working with integer values here I had to use ABS(). I tried to use CAST(tm.meta_value AS int) but I got an error, and ABS() works. The only 'bad thing' about this is that you cannot use negative integers, as absolute value will turn it to positive integer, but the workaround is that you don't use negative values :D

This is how you can sort a column that contains integer values in it, for other values you'll change the orderby part, but the gist is the same, and is better to store taxonomy meta values in the termmeta table, than in the options table as a serialized array. First, it's not serialized, second it get's deleted when you delete your term from the taxonomy ;)

Hope this helps to somebody stumbling upon this issue :)