我正在尝试使用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'
它工作正常,但它不会在顶部显示恶臭的帖子,任何修复它的方法。
在结果集中放置一列,显示featured
状态。我相信,你必须再次加入wp_postmeta才能实现这一目标。
然后使用
ORDER BY status, distance
或者也许
ORDER BY STATUS='featured', distance
这将使您的精选帖子首先按距离排序,然后按其余部分排序。
(在ORDER BY
子句中放置子查询很奇怪。
试试这个,我从子查询中选择了custom_order_by
列,并按此排序
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,
(SELECT wp.meta_value FROM wp_postmeta wp where wp.post_id=wpm.post_id AND wp.meta_key = 'featured_h') AS custom_order_by
FROM wp_posts
INNER JOIN wp_postmeta AS wpm 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 (wpm.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 custom_order_by ASC, distance ASC LIMIT 0, 10