0

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.

enter image description here

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

8
  • pre_get_terms isn't an opportunity to return a new set of results, it's an opportunity to modify the parameters on the WP_Term_Query object before it executes. That SQL query has no place in that action, there's been a misunderstanding
    – Tom J Nowell
    Feb 8 at 0:09
  • @TomJNowell I see... thanks for clarifying! I figured out that I can use apply_filters( 'terms_clauses', string[] $pieces, string[] $taxonomies, array $args ), but I can't understand how to apply my query to the $pieces. Do you have any idea?
    – jbmgil
    Feb 8 at 14:56
  • I don't believe that's necessary at all to achieve your goal, WP_Term_Query can already sort by meta, and that by focusing on the SQL you're severely limiting the number of people who are capable of answering the question. Needing to use those filters is usually a big red flag 🚩
    – Tom J Nowell
    Feb 8 at 15:36
  • @TomJNowell I understand that the WP_Term_Query can sort by meta value, but the problem is that this meta value, in my case, is the ID of another taxonomy term and I need to sort by the name of the term that has this ID. That's why I thought that I would have to use SQL, because I thought that I had to join the tables. I'm having a hard time trying to understand how to do this with meta_query.
    – jbmgil
    Feb 8 at 16:30
  • That's not good, and won't scale. Why is it the ID of another taxonomy term? Is there a reason the name of the term that you intend to sort by couldn't also be saved as term meta? This is going to be slow and will get slower as the table gets bigger. I'd look at alternative methods of storing this data, terms are for grouping objects e.g. post terms/comment terms/user terms, they're not meant to be used as "term terms", and you should consider this both technical debt and a code smell. It's hard to suggest an alternative without the context for what you were trying to build that lead to this.
    – Tom J Nowell
    Feb 8 at 16:34

2 Answers 2

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). enter image description here

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! enter image description here

0

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.

2
  • Thank you for your help! I still can't "up vote" your answer because I don't have enough reputation. Also, I will accept my own answer just because it describes the exact solution to the problem. But your answer and your comments were very helpful!
    – jbmgil
    Feb 8 at 19:54
  • that's fine, you answered your own question and wrote it up nicely! The only thing I would note is that NOT style queries can be expensive, so avoid NOT EXISTS if you can. Try to add empty values if possible so you don't need that clause
    – Tom J Nowell
    Feb 9 at 0:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.