MySQLで数値の昇順の後に空値のレコードが並ぶようにする

WordPressで構築したサイトで「カスタムフィールドに入力された金額の昇順に並び替えたい、かつ金額が未入力の情報も表示したい」という要望がありました。通常であれば下記のように金額の昇順に並ぶようにすれば良いのですが、空値がある場合だとその情報が優先して表示されてしまいました。

function my_pre_get_posts() {
    // 条件は状況に合わせて変更してください。
    if ( $query->is_main_query() && !is_admin() ) {
        $query->set( 'order', 'ASC' );
        $query->set( 'orderby', 'meta_value_num' );
        $query->set( 'meta_key', '並び替えに利用するカスタムフィールドのキー' );
    }
}
add_action( 'pre_get_posts', 'my_pre_get_posts' );

ORDERBY句を確認してみる

数値として並び替えているので、空値が0と判定されてしまったようです。実行されたSQLを確認してみると、ORDERBYの指定が次のようになっていました。

ORDER BY wp.meta_value+0 ASC

次のようなORDERBY句の指定にすることができれば、初めに数値の昇順のレコードが並び、その後に空値のレコードが続くようにすることができます。

ORDER BY wp.meta_value+0 > 0 DESC, wp.meta_value+0 ASC

フックを使ってORDERBY句を書き換える

WordPressの場合は実行されるクエリのORDERBY句を書き換えるフックがあるので、それを利用してORDERBY句を変更してあげれば良いです。条件などは実行されているクエリを確認して調整してみてください。クエリはDebug Barプラグインで確認できます。

// 下記の例はテーブル名が「wp_postmeta」の場合です。
function price_order( $query, $wp_query ) {
    if ( $query === 'wp_postmeta.meta_value+0 ASC' ) {
        $query = 'wp_postmeta.meta_value+0 > 0 DESC, ' . $query;
    }
    remove_filter( 'posts_orderby', 'price_order' );
    return $query;
}
function my_pre_get_posts() {
    // 条件は状況に合わせて変更してください。
    if ( $query->is_main_query() && !is_admin() ) {
        $query->set( 'order', 'ASC' );
        $query->set( 'orderby', 'meta_value_num' );
        $query->set( 'meta_key', '並び替えに利用するカスタムフィールドのキー' );
        // ORDERBY句を変更するための処理を追加
        add_filter( 'posts_orderby', 'price_order', 10, 2 );
    }
}
add_action( 'pre_get_posts', 'my_pre_get_posts' );