mysql - 带有 Wordpress 帖子和 post_meta 的子查询

Question

我知道这是不对的，尽管这是可行的。

我需要找到给定半径和位置附近的所有帖子(作为收入)，但我需要子查询 post_meta 中的经纬度信息。

它会伤害你的眼睛，所以在这里:

/*
lat -23.6480147
lng -46.704116399999975
radius 20km
6371 para km e 3959 para miles
*/
SELECT  vision_outlet.wp_posts.ID as 'ID', 
    vision_outlet.wp_posts.post_title as 'Nome', 
    (
        SELECT  vision_outlet.wp_postmeta.meta_value 
        FROM    vision_outlet.wp_postmeta
        WHERE   vision_outlet.wp_postmeta.meta_key = 'formatted_address' 
        AND vision_outlet.wp_postmeta.post_id = vision_outlet.wp_posts.ID
    ) as 'formatted_address' ,
    (
        SELECT  vision_outlet.wp_postmeta.meta_value 
        FROM    vision_outlet.wp_postmeta
        WHERE   vision_outlet.wp_postmeta.meta_key = 'lat' 
        AND vision_outlet.wp_postmeta.post_id = vision_outlet.wp_posts.ID
    ) as 'lat',
    (
        SELECT  vision_outlet.wp_postmeta.meta_value 
        FROM    vision_outlet.wp_postmeta
        WHERE   vision_outlet.wp_postmeta.meta_key = 'lng' 
        AND vision_outlet.wp_postmeta.post_id = vision_outlet.wp_posts.ID
    ) as 'lng',
    (
    6371 * acos( cos( radians(-23.6480147) ) * cos( radians
        ( 
            (
                SELECT  vision_outlet.wp_postmeta.meta_value 
                FROM    vision_outlet.wp_postmeta
                WHERE   vision_outlet.wp_postmeta.meta_key = 'lat' 
                AND vision_outlet.wp_postmeta.post_id = vision_outlet.wp_posts.ID
            )
        ) ) * cos( radians
        (  
            (
                SELECT  vision_outlet.wp_postmeta.meta_value 
                FROM    vision_outlet.wp_postmeta
                WHERE   vision_outlet.wp_postmeta.meta_key = 'lng' 
                AND vision_outlet.wp_postmeta.post_id = vision_outlet.wp_posts.ID
            )
        ) - radians(-46.704116399999975) ) + sin( radians(-23.6480147) ) * sin( radians
        ( 
            (
                SELECT  vision_outlet.wp_postmeta.meta_value 
                FROM    vision_outlet.wp_postmeta
                WHERE   vision_outlet.wp_postmeta.meta_key = 'lat' 
                AND vision_outlet.wp_postmeta.post_id = vision_outlet.wp_posts.ID
            )
        ) ) )
        ) as 'distance'

FROM vision_outlet.wp_posts, vision_outlet.wp_postmeta

WHERE vision_outlet.wp_posts.ID = vision_outlet.wp_postmeta.post_id
AND vision_outlet.wp_posts.post_status = 'publish'
AND vision_outlet.wp_postmeta.meta_key = 'formatted_address'
HAVING distance < 20
ORDER BY distance

问题是:我是否需要考虑创建一个新表，或者有什么方法可以重构它吗？

Answer 1

现在好多了

/*
lat -23.6480147
lng -46.704116399999975
radius 20km
6371 para km e 3959 para miles
*/
SELECT  wp_posts.ID, 
        wp_posts.post_title, 
        pm1.meta_value as formatted_address, 
        pm2.meta_value as lat, 
        pm3.meta_value as lng,
        6371 * acos( cos( radians(-23.6480147) ) * cos( radians( pm2.meta_value ) ) * cos( radians ( pm3.meta_value ) - radians(-46.704116399999975) ) + sin( radians(-23.6480147) ) * sin( radians ( pm2.meta_value ) ) )  as 'distance'

FROM wp_posts
LEFT JOIN wp_postmeta AS pm1 ON (wp_posts.ID = pm1.post_id AND pm1.meta_key='formatted_address')
LEFT JOIN wp_postmeta AS pm2 ON (wp_posts.ID = pm2.post_id AND pm2.meta_key='lat')
LEFT JOIN wp_postmeta AS pm3 ON (wp_posts.ID = pm3.post_id AND pm3.meta_key='lng')

WHERE wp_posts.post_type = 'outlet'
AND wp_posts.post_status = 'publish'
HAVING distance < 20
ORDER BY distance