WordPress中posts_orderby失效，改用pre_get_posts生效的原因咨询

这问题我之前踩过类似的坑，核心原因是你搞混了posts_orderby钩子的作用逻辑——它接收的参数已经是WordPress自动生成的ORDER BY子句的内容部分，而不是让你重新拼接整个ORDER BY语句。

具体问题分析 #

看你失效的posts_orderby代码：

function search_posts_orderby ($orderby) {
    global $wp_query, $wpdb;
    if (!is_admin() && $wp_query->is_search) {
        $post_title = "{$wpdb->prefix}posts.post_title";
        $post_date = "{$wpdb->prefix}posts.post_date";
        // 错误：直接追加了完整的ORDER BY语句
        $orderby .= " ORDER BY $post_title ASC,";
        $orderby .= " $post_date DESC";
    }
    return $orderby;
}

WordPress原本生成的SQL里已经有一个ORDER BY子句（比如默认的ORDER BY wp_posts.post_date DESC），你在$orderby后面直接追加ORDER BY ...，最终生成的SQL就会变成这样：

SELECT ... FROM wp_posts ... 
WHERE ... 
ORDER BY wp_posts.post_date DESC ORDER BY wp_posts.post_title ASC, wp_posts.post_date DESC

这明显是SQL语法错误，数据库执行失败，自然返回空结果。

为什么pre_get_posts能正常工作？ #

pre_get_posts是在WordPress构建查询之前触发的钩子，你通过$query->set()方法修改的是查询参数（orderby和order），WordPress会根据这些参数自动生成正确的ORDER BY子句，完全不会出现手动拼接SQL的语法冲突，这也是WordPress官方推荐的修改查询方式。

修正后的posts_orderby写法（如果一定要用这个钩子） #

如果你确实需要用posts_orderby实现更复杂的排序，只需要直接替换或修改$orderby的内容，不要加ORDER BY关键词：

function search_posts_orderby ($orderby) {
    global $wp_query, $wpdb;
    if (!is_admin() && $wp_query->is_search) {
        $post_title = "{$wpdb->prefix}posts.post_title";
        $post_date = "{$wpdb->prefix}posts.post_date";
        // 直接设置排序字段，不需要ORDER BY
        $orderby = "$post_title ASC, $post_date DESC";
    }
    return $orderby;
}
add_filter('posts_orderby', 'search_posts_orderby', 10, 2);

两种钩子的适用场景 #

• pre_get_posts：优先推荐，适合大多数查询修改需求，符合WordPress开发规范，避免手动写SQL的风险。
• posts_orderby：适合需要关联自定义表字段排序、或者非常复杂的排序逻辑的场景，但必须注意参数的格式要求。

你的相关代码整理：

1. 正常工作的posts_where钩子代码

function search_posts_where ($where) { 
    global $wp_query, $wpdb; 
    // Searching and not in admin 
    if (!is_admin() && $wp_query->is_search && isset($wp_query->query_vars['s'])) { 
        // Tables names 
        $post_title = "{$wpdb->prefix}posts.post_title"; 
        $post_excerpt = "{$wpdb->prefix}posts.post_excerpt"; 
        $post_content = "{$wpdb->prefix}posts.post_content"; 
        $post_type = "{$wpdb->prefix}posts.post_type"; 
        $post_status = "{$wpdb->prefix}posts.post_status"; 
        $post_author = "{$wpdb->prefix}posts.post_author"; 
        $post_ID = "{$wpdb->prefix}posts.ID"; 
        $post_date = "{$wpdb->prefix}posts.post_date"; 
        // Get the 's' parameters 
        $search_text = $wp_query->query_vars['s'] ?? 'IS NULL'; 
        // Write the where clause 
        $where = " AND ((($post_title LIKE '%$search_text%')"; 
        $where .= " OR ($post_excerpt LIKE '%$search_text%')"; 
        $where .= " OR ($post_content LIKE '%$search_text%')))"; 
        $where .= " AND $post_type IN ('parcours')"; 
        $where .= " AND $post_status = 'publish'"; 
        $where .= " GROUP BY $post_ID"; 
    } 
    return $where; 
} 
add_filter('posts_where', 'search_posts_where', 10, 2);

2. 正常工作的posts_join钩子代码

function search_posts_join ($join) { 
    global $wp_query, $wpdb; 
    // Searching and not in admin 
    if (!is_admin() && $wp_query->is_search && isset($wp_query->query_vars['s'])) { 
        // Tables names 
        $post_meta = "{$wpdb->prefix}postmeta"; 
        $post_ID = "{$wpdb->prefix}posts.ID"; 
        $post_meta_ID = "{$wpdb->prefix}postmeta.post_id"; 
        // Join clause 
        $join .= "LEFT JOIN $post_meta ON ($post_ID = $post_meta_ID)"; 
    } 
    return $join; 
} 
add_filter('posts_join', 'search_posts_join', 10, 2);

3. 失效的posts_orderby钩子代码（已分析问题）

function search_posts_orderby ($orderby) { 
    global $wp_query, $wpdb; 
    // Searching and not in admin 
    if (!is_admin() && $wp_query->is_search) { 
        // Tables names 
        $post_title = "{$wpdb->prefix}posts.post_title"; 
        $post_date = "{$wpdb->prefix}posts.post_date"; 
        $post_ID = "{$wpdb->prefix}posts.ID"; 
        // Order by clause 
        $orderby .= " ORDER BY $post_title ASC,"; 
        $orderby .= " $post_date DESC"; 
    } 
    return $orderby; 
} 
add_filter('posts_orderby', 'search_posts_orderby', 10, 2);

4. 生效的pre_get_posts钩子代码

function order_posts_by_title( $query ) { 
    if ( $query->is_search() && $query->is_main_query() ) { 
        $query->set( 'orderby', 'title' ); 
        $query->set( 'order', 'ASC' ); 
    } 
} 
add_action( 'pre_get_posts', 'order_posts_by_title' );

内容的提问来源于stack exchange，提问作者Olivier Paudex