CODEWITHSUNDEEP
mysql
topherg
topherg

Reputation: 4293

Applying a function to an aliased field

I have a query that creates two aliased files like this:

 (CASE WHEN `venueID` > 0 THEN `venueLongitude` ELSE `specialLongitude` END) AS `longitude`
 (CASE WHEN `venueID` > 0 THEN `venueLatitude` ELSE `specialLatitude` END) AS `latitude`

which determine which longitude and latitude to use in a certain query (there are numerous JOIN's in the table, but for the sake of this example, the only JOIN is from the Gigs table performing a NATURAL LEFT JOIN to a table called Venues.

That bit works fine and returns the venue co-ordinates I am interested in doing a distance calculation that I can then order by.

The distance field looks like this:

((ACOS(SIN($lat * PI() / 180) * SIN(`latitude` * PI() / 180) + COS($lat * PI() / 180) * COS(`latitude` * PI() / 180) * COS(($lon - `longitude`) * PI() / 180)) * 180 / PI()) * 60 * 1.1515)

But when I run the query, I get the error Unknown column 'latitude' in 'field list'. Does anyone know how I can perform such a lookup.

p.s.

Although I know I can use geospatial queries, I would like to know the answer in a way that could later be used for further queries, i.e. 

SELECT *,(CASE WHEN `a` > 0 THEN `a` ELSE `b` END) AS `x`,(`x` MOD 3) AS `y` FROM `Foo`

Upvotes: 0

Views: 62

Answers (2)

Aiias
Aiias

Reputation: 4748

If you want to SELECT the distance, then I believe you need to re-declare the case for the value. AKA: Rather than using SELECT col alias1, alias1 + 1 alias2 you have to do SELECT col alias1, col + 1 alias2. Take a look at this MySQL documentation on Where you can use column aliases.

If you just want to GROUP BY the distance, then you can use your aliases in your distance function. However, if you want to SELECT the distance, you need to replace the alias usage in your distance function with the evaluated CASE code.

ORDER BY using aliases:

SELECT
  (CASE WHEN `venueID` > 0 THEN `venueLongitude` ELSE `specialLongitude` END) longitude,
  (CASE WHEN `venueID` > 0 THEN `venueLatitude` ELSE `specialLatitude` END) latitude
FROM table_name
ORDER BY ((ACOS(SIN(latitude * PI() / 180) * SIN(latitude * PI() / 180) + COS(latitude * PI() / 180) * COS(latitude * PI() / 180) * COS(longitude * PI() / 180)) * 180 / PI()) * 60 * 1.1515)

SELECT cannot use the aliases (it gets messy!):

SELECT
  (CASE WHEN `venueID` > 0 THEN `venueLongitude` ELSE `specialLongitude` END) longitude,
  (CASE WHEN `venueID` > 0 THEN `venueLatitude` ELSE `specialLatitude` END) latitude,
  (
    ((ACOS(SIN((CASE WHEN `venueID` > 0 THEN `venueLatitude` ELSE `specialLatitude` END) * PI() / 180) * SIN((CASE WHEN `venueID` > 0 THEN `venueLatitude` ELSE `specialLatitude` END) * PI() / 180) + COS((CASE WHEN `venueID` > 0 THEN `venueLatitude` ELSE `specialLatitude` END) * PI() / 180) * COS((CASE WHEN `venueID` > 0 THEN `venueLatitude` ELSE `specialLatitude` END) * PI() / 180) * COS((CASE WHEN `venueID` > 0 THEN `venueLongitude` ELSE `specialLongitude` END) * PI() / 180)) * 180 / PI()) * 60 * 1.1515)
  ) distance
FROM table_name
ORDER BY distance

Upvotes: 1

Vincent MAURY
Vincent MAURY

Reputation: 253

2 ways :

a) replace the aliases with SQL

b) Encapsulate your first query by another one like this :

     SELECT 
          t.latitude,
          t.longitude, 
          ((ACOS(SIN($lat * PI() / 180) * SIN(t.latitude * PI() / 180) + COS($lat * PI() / 180) * COS(latitude * PI() / 180) * COS(($lon - t.longitude) * PI() / 180)) * 180 / PI()) * 60 * 1.1515) as distance

     FROM ( 
       SELECT ((CASE WHEN `venueID` > 0 THEN `venueLongitude` ELSE `specialLongitude` END) AS `longitude`, (CASE WHEN `venueID` > 0 THEN `venueLatitude` ELSE `specialLatitude` END) AS `latitude` FROM Gigs left join Venues on ....
     ) as t

Upvotes: 1

Related Questions