[wp-trac] [WordPress Trac] #55812: Custom admin columns don't sort string meta_key values when using MariaDB.

[WordPress Trac]

#55812: Custom admin columns don't sort string meta_key values when using MariaDB.
--------------------------------+-----------------------------
 Reporter:  kevincorrigan       |      Owner:  (none)
     Type:  defect (bug)        |     Status:  new
 Priority:  normal              |  Milestone:  Awaiting Review
Component:  Administration      |    Version:  6.0
 Severity:  normal              |   Keywords:
  Focuses:  ui, administration  |
--------------------------------+-----------------------------
 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
 */
    }}}


 2. Define the hooks to add a custom column. \\ \\'''functions.php'''

 {{{#!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' );

 }}}

 3. 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).

 4. 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.

-- 
Ticket URL: <https://core.trac.wordpress.org/ticket/55812>
WordPress Trac <https://core.trac.wordpress.org/>
WordPress publishing platform