php - WordPress 按距离和特色帖子查询排序

Question

我正在尝试使用 mysql 查询来按距离对帖子进行排序，比如说在半径 250 英里内，一切正常，除了我需要以特色帖子始终位于顶部的方式对帖子进行排序。这是我的 mysql 查询(我在 wordpress 上执行)。

    SELECT SQL_CALC_FOUND_ROWS wp_posts.*, ( 3959 * acos( cos( radians(40.140711) ) * cos(      radians( latitude.meta_value ) ) * cos( radians( longitude.meta_value ) - radians(-74.20619299999998) ) + sin( radians(40.140711) ) * sin( radians( latitude.meta_value ) ) ) ) AS distance , 
latitude.meta_value AS latitude , 
longitude.meta_value AS longitude
    FROM wp_posts 
    INNER JOIN wp_postmeta ON (wp_posts.ID = wp_postmeta.post_id) 
    INNER JOIN wp_postmeta AS latitude ON wp_posts.ID = latitude.post_id 
    INNER JOIN wp_postmeta AS longitude ON wp_posts.ID = longitude.post_id 
    WHERE 1=1 
    AND wp_posts.post_type = 'event' 
    AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'recurring') 
    AND (wp_postmeta.meta_key = 'st_date' ) 
    AND latitude.meta_key="geo_latitude" 
    AND longitude.meta_key="geo_longitude" 
    AND wp_posts.ID in (SELECT tr.object_id FROM wp_term_relationships tr JOIN wp_term_taxonomy t on t.term_taxonomy_id=tr.term_taxonomy_id WHERE t.term_id IN ('3','4','5','6','7','8','9','10')) 
    AND (wp_posts.ID IN (SELECT wp_postmeta.post_id FROM wp_postmeta WHERE wp_postmeta.meta_key='st_date' 
    AND date_format(wp_postmeta.meta_value,'%Y-%m-%d') >'2013-09-23 14:35:58')) 
    GROUP BY wp_posts.ID HAVING distance <= 250 
    ORDER BY (SELECT wp_postmeta.meta_value FROM wp_postmeta, wp_posts AS p where wp_postmeta.post_id=p.ID AND wp_postmeta.meta_key = 'featured_h') ASC, distance ASC LIMIT 0, 10

问题出在 ORDER BY 上，它给我错误“子查询返回超过 1 行”

如果我删除 ORDER BY(从 wp_postmeta 选择 wp_postmeta.meta_value，wp_posts 作为 p，其中 wp_postmeta.post_id=p.ID 且 wp_postmeta.meta_key = 'featured_h'

它工作正常，但它不会在顶部显示热门帖子，任何修复它的方法。

Answer 1

在结果集中放置一列，显示精选状态。我相信，您必须再次加入 wp_postmeta 才能实现这一目标。

然后使用

 ORDER BY status, distance

或者也许

 ORDER BY STATUS='featured', distance

这会将您的精选帖子首先按距离排序，然后是其余帖子。

(将子查询放在 ORDER BY 子句中是很奇怪的。)