Using two posts_orderby add_filter makes conflict

Question

I'm trying to sort two cpt posts with term_order by using add_filter with posts_order hook. But it seems that the second filter override the first. How can i avoid this ? Do I have to create custom posts_orderby hooks ? Thanks for your help !

function ts_sort_package_in_categorie($orderby, $query)
{
    
    if (!ts_is_package_categorie_tax_query($query)) return;
    global $wpdb;
    return "{$wpdb->term_relationships}.term_order ASC";
    
}
add_filter('posts_orderby', 'ts_sort_package_in_categorie', 100, 2);

function ts_is_package_categorie_tax_query($query)
{
    if (empty($query->tax_query)) return;
    if (empty($query->tax_query->queries)) return;
    return in_array(
        $query->tax_query->queries[0]['taxonomy'],
        ['package_categorie'],
        true
    );
}

and :

function ts_sort_ticket_in_categorie($orderby, $query)
{
    
    if (!ts_is_ticket_categorie_tax_query($query)) return;
    global $wpdb;
    return "{$wpdb->term_relationships}.term_order ASC";
    
    
}
add_filter('posts_orderby', 'ts_sort_ticket_in_categorie', 99, 2);

function ts_is_ticket_categorie_tax_query($query)
{
    if (empty($query->tax_query)) return;
    if (empty($query->tax_query->queries)) return;
    return in_array(
        $query->tax_query->queries[0]['taxonomy'],
        ['ticket_categorie'],
        true
    );
}

I also tried something like this with no results :

function ts_sort_ticket_package_in_categorie($orderby, $query)
{
    if (!ts_is_package_categorie_tax_query($query)) return;
    global $wpdb;
    return "{$wpdb->term_relationships}.term_order ASC";
    
    
    if (!ts_is_ticket_categorie_tax_query($query)) return;
    global $wpdb;
    return "{$wpdb->term_relationships}.term_order ASC";
        
        
    }
add_filter('posts_orderby', 'ts_sort_ticket_package_in_categorie', 99, 2);

function ts_is_ticket_categorie_tax_query($query)
{
    if (empty($query->tax_query)) return;
    if (empty($query->tax_query->queries)) return;
    return in_array(
        $query->tax_query->queries[0]['taxonomy'],
        ['ticket_categorie'],
        true
    );
}
function ts_is_package_categorie_tax_query($query)
{
    if (empty($query->tax_query)) return;
    if (empty($query->tax_query->queries)) return;
    return in_array(
        $query->tax_query->queries[0]['taxonomy'],
        ['package_categorie'],
        true
    );
}

Answer 1

In the database definition of WP, in the table that links posts to terms, there is a term_order field. But this term_order is the order in which the terms are assigned to the post, NOT the order in which posts are assigned to a term.

You can see the intent behind the term_order field in this WordPress core ticket: https://core.trac.wordpress.org/ticket/5857

Instead, modify the output of WP_Query with a taxonomy query, and order by date.