posts_clauses is where you fix a query WP_Query cannot express

WP_Query covers a lot and stops short of a few things: ordering by a joined table, a GROUP BY you chose, an index hint, a comparison meta_query cannot express. posts_clauses hands you every fragment of the query as an array just before it is assembled, along with the WP_Query object so you can tell which query you are looking at.

add_filter( 'posts_clauses', function ( $clauses, $query ) {
    if ( ! $query->get( 'catalogue_order_by_stock' ) ) {
        return $clauses;
    }

    global $wpdb;

    $clauses['join'] .= " INNER JOIN {$wpdb->postmeta} stock"
        . " ON stock.post_id = {$wpdb->posts}.ID AND stock.meta_key = '_stock' ";

    $clauses['orderby'] = ' CAST(stock.meta_value AS SIGNED) DESC, ' . $clauses['orderby'];
    $clauses['groupby'] = "{$wpdb->posts}.ID";

    return $clauses;
}, 10, 2 );

The second argument is what makes this usable. Filtering posts_where or posts_join on their own gives you a string and no way to know whether this is the main query, a widget or the third sidebar loop — so the change lands on all of them. Gating on a custom query var set by the caller keeps it to the one query that asked. Two costs, both real. You now own a fragment of SQL, which means anything arriving from a request has to go through $wpdb->prepare() yourself, since nothing downstream will do it for you. And the clause names are internal: a WordPress release that restructures the query builder breaks this quietly, so it belongs in a small, obvious place with a comment saying which version it was written against.