带有Wordpress帖子和post_meta的子查询

I know this is not right, although this is working.

我知道这是不对的,虽然这是有效的。

I need to find all posts (as revenue) that is near from a given radius and location, but I need to subquery the latitude and longitude info which is in post_meta.

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

It's gonna hurt your eyes, so here it is:

它会伤到你的眼睛,所以这里是:

/*
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

The question is: Should I need to consider creating a new table or is there any way refactor this?

问题是:我是否需要考虑创建一个新表,还是有任何重构方法?

1 个解决方案

#1

Now it's better:

现在好了:

/*
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