mysql - WordPress 和 Haversine 公式

Question

我正在尝试在 WordPress 中使用 Haversine 公式，使用自定义帖子类型“stores”，自定义字段包含 latitude、longitude 和 street_address 仅供显示。我一直在用this tutorial作为指南。

这是我正在尝试计算的查询...

SELECT wp_posts.ID, 
       wp_posts.post_title, 
       pm1.meta_value as address, 
       pm2.meta_value as latitude, 
       pm3.meta_value as longitude, ( 6371 * acos( cos( radians(37) ) * cos( radians( latitude ) ) * cos( radians( longitude ) - radians(-122) ) + sin( radians(37) ) * sin( radians( latitude ) ) ) ) AS distance   
FROM wp_posts 
LEFT JOIN wp_postmeta AS pm1 ON (wp_posts.ID = pm1.post_id AND pm1.meta_key='street_address')
LEFT JOIN wp_postmeta AS pm2 ON (wp_posts.ID = pm2.post_id AND pm2.meta_key='latitude') 
LEFT JOIN wp_postmeta AS pm3 ON (wp_posts.ID = pm3.post_id AND pm3.meta_key='longitude') 
WHERE wp_posts.post_type = 'stores' 
AND wp_posts.post_status = 'publish' 
HAVING distance < 25 
ORDER BY distance 
LIMIT 0 , 20

但这会返回...

Unknown column 'latitude' in 'field list'

我也尝试过使用 query here 的变体，但我得到关于“未知列”的相同错误。

非常感谢任何建议!

Answer 1

尝试在公式中使用原始列名而不是新别名，例如 latitude =>pm2.meta_value 和 latitude=>pm3.meta_value

SELECT wp_posts.ID, 
       wp_posts.post_title, 
       pm1.meta_value AS address, 
       pm2.meta_value AS latitude, 
       pm3.meta_value AS longitude, 
       ( 6371 * ACOS( COS( RADIANS(37) ) * COS( RADIANS( pm2.meta_value ) ) * COS( RADIANS( pm3.meta_value ) - RADIANS(-122) ) + SIN( RADIANS(37) ) * 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='street_address')
LEFT JOIN wp_postmeta AS pm2 ON (wp_posts.ID = pm2.post_id AND pm2.meta_key='latitude') 
LEFT JOIN wp_postmeta AS pm3 ON (wp_posts.ID = pm3.post_id AND pm3.meta_key='longitude') 
WHERE wp_posts.post_type = 'stores' 
AND wp_posts.post_status = 'publish' 
HAVING distance < 25 
ORDER BY distance 
LIMIT 0 , 20

编辑

SELECT 
  wp_posts.ID,
  wp_posts.post_title,
  pm1.meta_value AS address,
  pm2.meta_value AS latitude,
  pm3.meta_value AS longitude,
  (
    6371 * ACOS(
      COS(RADIANS(37)) * COS(
        RADIANS(
          CASE
            WHEN pm2.meta_value = '' 
            THEN 0 
            WHEN pm2.meta_value IS NULL 
            THEN 0 
            ELSE pm2.meta_value 
          END
        )
      ) * COS(
        RADIANS(
          CASE
            WHEN pm3.meta_value = '' 
            THEN 0 
            WHEN pm3.meta_value IS NULL 
            THEN 0 
            ELSE pm3.meta_value 
          END
        ) - RADIANS(- 122)
      ) + SIN(RADIANS(37)) * SIN(
        RADIANS(
          CASE
            WHEN pm2.meta_value = '' 
            THEN 0 
            WHEN pm2.meta_value IS NULL 
            THEN 0 
            ELSE pm2.meta_value 
          END
        )
      )
    )
  ) AS distance 
FROM
  wp_posts 
  LEFT JOIN wp_postmeta AS pm1 
    ON (
      wp_posts.ID = pm1.post_id 
      AND pm1.meta_key = 'street_address'
    ) 
  LEFT JOIN wp_postmeta AS pm2 
    ON (
      wp_posts.ID = pm2.post_id 
      AND pm2.meta_key = 'latitude'
    ) 
  LEFT JOIN wp_postmeta AS pm3 
    ON (
      wp_posts.ID = pm3.post_id 
      AND pm3.meta_key = 'longitude'
    ) 
WHERE wp_posts.post_type = 'stores' 
  AND wp_posts.post_status = 'publish' 
HAVING distance < 25 
ORDER BY distance 
LIMIT 0, 20