How to use a MySQL column alias for calculations?

6
votes

How can I use my column alias (lat and lng) from the two subqueries to make the distance calcuation underneath? What I am basically trying to do is is to calculate the distance between two locations using longitude and latitude values. But somehow my aliases aren't usable in the query, why?

SELECT wp_posts.*,
(SELECT wp_postmeta.meta_value FROM wp_postmeta WHERE wp_postmeta.post_id = wp_posts.ID   AND wp_postmeta.meta_value LIKE '41.%') AS lat,
(SELECT wp_postmeta.meta_value FROM wp_postmeta WHERE wp_postmeta.post_id = wp_posts.ID AND wp_postmeta.meta_value LIKE '2.%') AS lng,
(3959 * acos( cos( radians(41.367682) ) * cos( radians( 'lat' ) ) * cos( radians('lng') -     radians(2.154077)) + sin(radians(41.367682)) * sin( radians('lat')))) AS distance
FROM wp_posts, wp_postmeta
WHERE wp_posts.ID = wp_postmeta.post_id AND wp_postmeta.meta_key = 'position' AND wp_posts.post_status = 'publish' AND wp_posts.post_type = 'page' AND wp_posts.post_date <  NOW()
GROUP BY ID
ORDER BY distance ASC
2
mysqlsubqueryaliascalculated-columns
Why do you put lat and lng in quotes in your expression? - Sergio Tulentsev
Does it run without any errors or do you get some errors while executing this query? - Lion
The quotes are needed to run the query, I get no errors and everything is returned correctly except that the distance calculation returns the same value for all records, meaning that the values entered are not used for anything. What am I doing wrong here? - Chris

2 Answers

4
votes

OK, What you need to do here is join the same table (wp_postmeta) twice under different aliases so you can use different 'WHERE' conditions. I don't have your tables so I can't test this, but this is the approach you'll want to use:

SELECT wp_posts.*,
     (`alias_1`.meta_value) AS `lat`,
     (`alias_2`.meta_value) AS `lng`,
     (3959 * acos( cos( radians(41.367682) ) * cos( radians( `lat` ) ) * cos( radians( `lng` ) -     radians(2.154077)) + sin(radians(41.367682)) * sin( radians( `lat` )))) AS `distance`
FROM wp_posts
     LEFT JOIN `wp_postmeta` AS `alias_1` ON wp_posts.ID = alias_1.post_id
     LEFT JOIN `wp_postmeta` AS `alias_2` ON wp_posts.ID = alias_2.post_id
WHERE 
     wp_posts.post_status = 'publish' 
     AND wp_posts.post_type = 'page' 
     AND wp_posts.post_date <  NOW()
     AND `alias_1`.meta_key = 'position'
     AND `alias_1`.meta_value LIKE '41.%'
     AND `alias_2`.meta_key = 'position'
     AND `alias_2`.meta_value LIKE '2.%'
GROUP BY wp_posts.`ID`
ORDER BY `distance` ASC

I may have some syntax errors in there, but I believe that the logic is roughly correct. Let me know if this works.

1
votes

Once you've created your alias (which you should add quotes before and after) you should not reference it as a string... subsequent references to the alias should be enclosed in backticks:

SELECT wp_posts.*,
(SELECT wp_postmeta.meta_value FROM wp_postmeta WHERE wp_postmeta.post_id = wp_posts.ID   AND wp_postmeta.meta_value LIKE '41.%') AS 'lat',
(SELECT wp_postmeta.meta_value FROM wp_postmeta WHERE wp_postmeta.post_id = wp_posts.ID AND wp_postmeta.meta_value LIKE '2.%') AS 'lng',
(3959 * acos( cos( radians(41.367682) ) * cos( radians( `lat` ) ) * cos( radians(`lng`) -     radians(2.154077)) + sin(radians(41.367682)) * sin( radians(`lat`)))) AS `distance`
FROM wp_posts, wp_postmeta
WHERE wp_posts.ID = wp_postmeta.post_id AND wp_postmeta.meta_key = 'position' AND wp_posts.post_status = 'publish' AND wp_posts.post_type = 'page' AND wp_posts.post_date <  NOW()
GROUP BY `ID`
ORDER BY 'distance' ASC

http://dev.mysql.com/doc/refman/5.0/en/problems-with-alias.html