BUG: terms incorrectly indexed when ORDER BY t.term_order is applied

[katag9k]

Describe the bug

When the ORDER BY t.term_order clause is applied via the terms_clauses filter, not all terms are indexed. For example, the taxonomy-terms-order plugin sets

$clauses['orderby'] = 'ORDER BY term_order';

This causes only about half of the terms to be indexed. Removing this filter allows the indexing process to complete properly.

Steps to Reproduce

add the filter to your theme

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

function test_apply_order_filter($clauses, $taxonomies, $args) {
    // Set the orderby clause to sort by term order
    $clauses['orderby'] = 'ORDER BY term_order';
    return $clauses;
}

Try indexing terms, although the message will appear that all terms are indexed the actual count of the indexed terms is off. Out of 121,319 terms, only about 69,650 were indexed.

WordPress and ElasticPress information

WordPress 6.7.2
Elasticsearch - 7.17.8
WPVIP

Code of Conduct

• I agree to follow this project's Code of Conduct

[burhandodhy]

Hi @katag9k,

Would you mind explaining what you're trying to achieve? I tested the code you shared, and it throws a database error [Unknown column 't.term_order' in 'ORDER BY' clause].

Regards,
Burhan

[katag9k]

@burhandodhy Apologies, it should be term_order, not t.term_order. This filter comes from a third-party plugin called Category Order and Taxonomy Terms Order. When the plugin is active with this filter, not all terms are indexed during bulk indexing. I suspect that the terms_clauses filter needs to be removed to ensure the correct ORDER BY id behaviour.

remove_all_filters('terms_clauses');

Maybe here somewhere:

ElasticPress/includes/classes/Indexable/Term/Term.php

Line 181 in 377fb88

public function query_db( $args ) {

?

[burhandodhy]

@katag9k You need to prevent the WHERE clause from taking effect during the sync process. I reviewed the plugin code and found that it includes a filter to/get_terms_orderby/ignore, which allows you to bypass the WHERE clause during sync.

Also. ElasticPress provides a helper function, is_indexing https://github.com/10up/ElasticPress/blob/develop/includes/utils.php#L237 which determines whether indexing is in progress. Utilize this function within the filter to skip the plugin's WHERE clause during sync.

The final code will look something like this

add_filter( 'to/get_terms_orderby/ignore', function( $ignore ) {
    if ( \ElasticPress\Utils\is_indexing() ) {
        return true;
    }

    return $ignore;
} );

[katag9k]

@burhandodhy this solution only addresses one specific plugin. Other plugins or custom code may also modify via terms_clauses, leading to similar indexing issues, and not all of them may provide hooks to adjust this behaviour. Instead of relying on individual workarounds, a better approach in my opinion would be to ensure that term ordering does not interfere with the indexing process. This would help prevent incomplete indexing and maintain consistency, regardless of third-party modifications and save time in troubleshooting each time a client experiences this issue.