CODEWITHSUNDEEP
mysql
ShoeLace1291
ShoeLace1291

Reputation: 4698

Why is the column distance unknown in the where clause of my MySQL query?

I am currently trying to use the Haversine formula to make a locator for my business directory site. My query currently returns the error Unknown column distance in where clause. I have no idea why. This should work since I have defined the distance alias directly after the Haversine formula. Any ideas?

SELECT 
     l.listing_id AS id, l.category_id, l.title AS listing_title, l.description, l.address, l.city, l.zip, UNIX_TIMESTAMP(l.date_submitted) AS date_submitted, l.latitude, l.longitude,
     ( 6371 * acos( cos( radians(40.293861) ) * cos( radians( l.latitude ) ) * cos( radians( l.longitude ) - radians(-76.600252) ) + sin( radians(40.293861) ) * sin( radians( l.longitude ) ) ) ) AS distance, 
     c.category_id AS cat_id, c.title AS cat_title, c.slug AS cat_slug, 
     r.region_id AS region_id, r.title AS region_title, r.slug AS region_slug 
FROM listings AS l
LEFT JOIN categories AS c ON l.category_id = c.category_id
LEFT JOIN regions AS r ON l.region_id = r.region_id 
WHERE distance < 10
ORDER BY l.date_submitted DESC

Upvotes: 1

Views: 394

Answers (4)

Utsav
Utsav

Reputation: 8093

So as already pointed out in other answers, there are 3 generic ways which I can think of. I tried all in this fiddle

http://sqlfiddle.com/#!9/c8e411/6

  • Use a subquery do derive the column and use where in outer query.

  • Use the same condition in where as u used to derive the column

  • Use group by on all the columns and use having clause. If query is already using group by then you have to adjust it accordingly.

Upvotes: 0

Paul Maxwell
Paul Maxwell

Reputation: 35583

The most common workaround is to treat the query as a derived table, then use the column alias in the outer query, e.g.

SELECT
      d.*
FROM (
      SELECT
            l.listing_id AS id
          , l.category_id
          , l.title AS listing_title
          , l.description
          , l.address
          , l.city
          , l.zip
          , UNIX_TIMESTAMP(l.date_submitted) AS date_submitted
          , l.latitude
          , l.longitude
          , (6371 * ACOS(COS(RADIANS(40.293861)) * COS(RADIANS(l.latitude)) * COS(RADIANS(l.longitude) - RADIANS(-76.600252)) + SIN(RADIANS(40.293861)) * SIN(RADIANS(l.longitude)))) AS distance
          , c.category_id AS cat_id
          , c.title AS cat_title
          , c.slug AS cat_slug
          , r.region_id AS region_id
          , r.title AS region_title
          , r.slug AS region_slug
      FROM listings AS l
            LEFT JOIN categories AS c ON l.category_id = c.category_id
            LEFT JOIN regions AS r ON l.region_id = r.region_id
      ) d
WHERE d.distance < 10
ORDER BY
      d.date_submitted DESC

A HAVING clause is not a substitute for the where clause, the HAVING clause exists to allow filtering by aggregated values (e.g. having SUM(sales) > 1000 )

Upvotes: 1

Nico Andrade
Nico Andrade

Reputation: 910

As said above, you can not use an alias in the WHERE clause because the value is still not known.

What I want to point out is that, this query as you show it, won't scale up as you have more and more rows, since MySQL won't have any way to narrow down the number of possible matches.

A good approach will be to add a WHERE condition you'll use to define a square area having the circle area inside. In that way your SQL won't scan the whole world, but will filter those rows where the area is "close enough" to the circle area; and then calculate the distance lower than 10.

For example, if you're looking for distance 10 of a point X,Y; you can say

WHERE
latitude < (X+5) AND latitude > (X-5) AND longitude < (Y+5) AND longitude > (Y-5)

This means you're ignoring a set of rows, and then doing the expensive calculations over a subset of rows; which is much faster than doing the expensive calculations over the entire dataset.

I hope this helps you to get a faster and more scalable application.

Upvotes: 1

C3roe
C3roe

Reputation: 96306

You simply can not use an ALIAS in the WHERE clause.

MySQL Manual, B.5.4.4 Problems with Column Aliases:

“Standard SQL disallows references to column aliases in a WHERE clause. This restriction is imposed because when the WHERE clause is evaluated, the column value may not yet have been determined.”

You will have to repeat the formula that calculates the value in your WHERE clause.

Upvotes: 0

Related Questions