Make WordPress Core

Opened 2 years ago

Last modified 2 years ago

#55812 new defect (bug)

Custom admin columns don't sort string meta_key values when using MariaDB.

Reported by: kevincorrigan's profile kevincorrigan Owned by:
Milestone: Awaiting Review Priority: normal
Severity: normal Version: 6.0
Component: Administration Keywords:
Focuses: ui, administration Cc:

Description

If your using MariaDB, and you want to sort the WP_Query using meta_key and orderby 'meta_value' sort does not work.

To reproduce:

  1. Create a basic child theme, I made a child theme of Twenty Twenty Two that contained the style.css and functions.php.

    style.css
/*
 Theme Name:   Twenty Twenty Two Child
 Description:  Twenty Twenty Two Child Theme
 Template:     twentytwentytwo
 Version:      1.0.0
 Text Domain:  twentytwentytwochild
*/
  1. Define the hooks to add a custom column.

    functions.php
<?php
function child_manage_pages_columns( $columns ) {
        return array_merge(
                array_slice( $columns, 1, 1 ),
                array(
                        'test_custom_field' => __( 'Test', 'twentytwentytwochild' ),
                ),
                array_slice( $columns, 1 ),
        );
}
add_filter( 'manage_pages_columns', 'child_manage_pages_columns' );

function child_pages_custom_columns( $column_key, $post_id ) {
        switch ( $column_key ) {
                case 'test_custom_field':
                        $value = get_post_meta( $post_id, 'test_custom_field', true );
                        echo esc_html( $value );
                        break;
        }
}
add_action( 'manage_pages_custom_column', 'child_pages_custom_columns', 10, 2 );

function child_manage_edit_pages_sortable_columns( $columns ) {
        $columns['test_custom_field'] = 'test_custom_field';
        return $columns;
}
add_filter( 'manage_edit-page_sortable_columns', 'child_manage_edit_pages_sortable_columns' );

function child_manage_edit_pages_sortable_columns_pre_get_posts( $query ) {
        if ( ! is_admin() || ! is_main_query() ) {
                return;
        }

        $orderby = $query->get( 'orderby' );
        switch ( $orderby ) {
                case 'test_custom_field':
                        $query->set( 'meta_key', 'test_custom_field' );
                        $query->set( 'orderby', 'meta_value' );
                        break;
        }
}
add_action( 'pre_get_posts', 'child_manage_edit_pages_sortable_columns_pre_get_posts' );

  1. Log into the WordPress admin panel and add a couple of pages that use custom fields. I made three pages two of those pages had the same custom field value and the third had a new value (Hello World, Hello World, and Other World).
  1. Go to the page listing and try to sort with the 'Test' column. The sort may change the first time, but will remain the same each time after that.

I tested this with MariaDB version 10.5.4 on Windows 10 with WAMP. I also tested with MySQL 8.0.24 on Windows 10 with WAMP but did not have the issue.

Through some testing and google searches I realized the issue is that MariaDB's LongText column type is just not sortable. In my test I updated wp_postmeta's meta_value column to Varchar(15000) and that enabled sort to work as expected.

Change History (1)

This ticket was mentioned in Slack in #core-test by ironprogrammer. View the logs.


2 years ago

Note: See TracTickets for help on using tickets.