List custom queried terms in the edit-tags.php?taxonomy page

Question

This is my first time posting a question here. I have this problem that I feel that I am soooo close to solve, but can't manage to do it.

I have a custom taxonomy called "aplication" and another called "segment". Each "aplication" term has an "segment" term associated with it trought a custom field (ACF).

What I'm trying to do is to sort the "aplication" terms alphabetically by their associated "segment" term in the edit-tag?taxonomy=aplication page.

I managed to add the sortable "segment" column into the page through the code below:

// Add the "segment" column to the list of "aplication" terms
add_filter('manage_edit-aplication_columns', function( $columns ) {

    $columns['aplication_segment'] =  __( 'Segment', 'textdomain' );

return $columns;
});


// Add data to the "segment" column created above
add_action( 'manage_aplication_custom_column', function( $value, $column, $aplication_id ) {

    if ( $column == 'aplication_segment') {

        $segment_ID  = get_field( 'aplication_segment', get_term($aplication_id, 'aplication') ); // The custom field for the aplications returns the segment ID
        $segment     = get_term( $segment_ID, 'segment' );
        $value = $segment -> name;

        echo $segment->name;
    }
}, 10, 3);


// Make the "segment" column sortable
add_filter('manage_edit-aplication_sortable_columns', function( $columns ) {

    $columns['aplication_segment'] = 'aplication_segment';

    return $columns;
});

Now, to sort the "aplication" terms I did this:

add_action('pre_get_terms', function( $term_query ) {

    global $current_screen;
    global $wpdb;    

    if ( ($current_screen) && $current_screen->id === 'edit-aplication' ) {

        if ( $term_query -> query_vars['orderby'] === 'aplication_segment' ) {
                            
            $sql =
                "SELECT ttm.term_id, ttm.name, ttm.slug, ttm.term_group, ttm.term_order
                FROM 
                    (SELECT wp_terms.term_id, wp_terms.name, wp_term_taxonomy.taxonomy
                        FROM wp_terms 
                        INNER JOIN wp_term_taxonomy 
                        ON wp_terms.term_id = wp_term_taxonomy.term_id
                        AND wp_term_taxonomy.taxonomy = 'segment'
                        ORDER BY wp_terms.name) as ttt
                INNER JOIN
                    (SELECT wp_terms.*, wp_termmeta.meta_value        
                        FROM wp_terms 
                        INNER JOIN wp_termmeta 
                        ON wp_terms.term_id = wp_termmeta.term_id
                        AND wp_termmeta.meta_key = 'aplication_segment'
                        ORDER BY wp_terms.name) as ttm
                ON ttt.term_id = ttm.meta_value 
                ORDER BY ttt.name {$term_query->query_vars['order']}";

            return $wpdb -> get_results( $sql );
        };
    }    

}, 10, 1);

This query works, it gives me the result that I need (I didn't translated those to english):

Array
(
    [0] => stdClass Object
        (
            [term_id] => 147
            [name] => Puxadores
            [slug] => puxadores-moveleiros
            [term_group] => 0
            [term_order] => 0
        )

    [1] => stdClass Object
        (
            [term_id] => 3391
            [name] => Cantoneiras
            [slug] => cantoneiras
            [term_group] => 0
            [term_order] => 0
        )

    [2] => stdClass Object
        (
            [term_id] => 150
            [name] => Automotivo
            [slug] => automotivo
            [term_group] => 0
            [term_order] => 0
        )

    [3] => stdClass Object
        (
            [term_id] => 149
            [name] => Dissipadores
            [slug] => dissipadores
            [term_group] => 0
            [term_order] => 0
        )

    [4] => stdClass Object
        (
            [term_id] => 3393
            [name] => Luminárias
            [slug] => luminarias
            [term_group] => 0
            [term_order] => 0
        )

    [5] => stdClass Object
        (
            [term_id] => 148
            [name] => Base Divisória
            [slug] => base-divisoria
            [term_group] => 0
            [term_order] => 0
        )

    [6] => stdClass Object
        (
            [term_id] => 3392
            [name] => Esquadrias
            [slug] => esquadrias
            [term_group] => 0
            [term_order] => 0
        )

)

The only problem is that I can't manage to show the result in the "aplication" terms table. It keeps sorting by the "aplication" term name.

I appreciate anyone who can try and help me with this.

Answer 1

Following @TomJNowell advices I descarted the usage of SQL query and aimed at the meta_value sorting. I'll describe the scenario from the beggining with the solution.

I have a custom taxonomy called "aplication" and another called "segment", and every application term has a custom field (ACF) for a segment term. What I needed was to sort the aplication terms by their associated segment term's name at the edit-tags.php?taxonomy page, but the problem was that the custom field value withhold the ID of the segment term and not it's name.

So, to workaround this problem, I added another custom field to the aplications, a text field that receives the slug of the segment. This field is automatically updated with the segment's slug every time that the aplication term is saved.

add_action( 'saved_aplication', 'update_aplication_segment_name', 10, 3);
function update_aplication_segment_name( $term_id, $tt_id, $update ) {
 
     $aplication = get_term( $term_id, 'aplication' ); //Gets the object of the current aplication
     $segment_ID = get_field( 'aplication_segment', $aplication ); //The segment is associated to the aplication through a custom field that returns the segment term's ID
     $segment    = get_term( $segment_ID, 'segment' ); //Gets the segment associated with the current aplication
     
     //Remove the hook to avoid loop
     remove_action( 'saved_aplication', 'update_aplication_segment_name', 10, 3);
 
     //Updates the value of the 'aplication_segment_name' field of the current aplication
     update_term_meta( $term_id, 'aplication_segment_name', $segment->slug);    
 
     //Add the hook back
     add_action( 'saved_aplication', 'update_aplication_segment_name', 10, 3);
}

With the code above, every time that the red field changes, the value of the blue field will change too (the blue field will be hidden from the final user).

Now, to sort the application terms by their segment's name I adapted the code from this answer: https://wordpress.stackexchange.com/a/277755/218755

add_filter('pre_get_terms', function( $term_query ) {
  
    global $current_screen;

   if ( is_admin() && $current_screen->id == 'edit-aplication' 
                   && ( !isset($_GET['orderby']) || $_GET['orderby'] == 'aplication_segment')) {

    // set orderby to the named clause in the meta_query
    $term_query -> query_vars['orderby'] = 'order_clause';
    $term_query -> query_vars['order'] = isset($_GET['order']) ? $_GET['order'] : "DESC";

    // the OR relation and the NOT EXISTS clause allow for terms without a meta_value at all
    $args = array('relation' => 'OR',
                  'order_clause' => array('key' => 'aplication_segment_name',
                                          'type' => 'STRING'
                                        ),
            array('key' => 'aplication_segment_name',
                  'compare' => 'NOT EXISTS'
      )
    );

    $term_query -> meta_query = new WP_Meta_Query( $args );

  }

  return $term_query;
  
});

Now the aplication terms can be sorted by their associated segment term's name!

Answer 2

You would not use SQL to do this, and that filter has nothing to do with SQL queries. This is why your code does not work as the returned value is discarded.

In general, if you have to write SQL to do something in WordPress then you're either working with custom tables, or something has gone horribly wrong in the development process. Usually efforts to write complicated SQL queries are because the developer is unaware of functions or APIs that do this for them but faster. This is what has happened here.

What pre_get_terms actually does

pre_get_terms allows you to modify the parameters of a WP_Term_Query before it builds the query and executes it. The incorrect usage of the action becomes clearer if we add proper type hinting to the function:

add_action('pre_get_terms', function( \WP_Term_Query $term_query ) : void {

Actions do not return values, filters return values. This action provides the term query object so that parameters can be changed/removed/set, but does not return values or results.

Sorting by multiple meta

There are already parameters for doing exactly what you want to do. For example to order by 2 meta fields, state, then city, you can do something like this:

$term_query->set( 'orderby', [
    'city_clause' => 'ASC',
    'state_clause' => 'DESC',
] );
$term_query->set( 'meta_query', [
    'relation' => 'AND',
    'state_clause' => [
        'key' => 'state',
        'value' => 'Wisconsin',
    ],
    'city_clause' => [
        'key' => 'city',
        'compare' => 'EXISTS',
    ], 
] );

This was adapted from an example in the official documentation for WP_Meta_Query on wordpress.org. Usage in WP_Query and WP_Comment_Query would be similar if not identical.

Writing SQL is unnecessary, and can even be counterproductive here, WordPress already provides this functionality. As a bonus this will work with pagination, the SQL query in the question will not. It will also work with the other functionality on that page such as filters and searches.