How to add condition on virtual column in mysql?

Question

I want to add a condition for the tbl_restaurant_featured_history.id column but I can't add that condition in where clause because It shows an error saying Unknown column 'featured' in 'where clause' and If I add a condition featured is not null in having clause It is returning 0 rows.

Below is the query before adding the condition

SELECT 
  DISTINCT(tbl_restaurant.id) as restaurant_id,  
  tbl_restaurant.name,  
  tbl_restaurant_featured_history.id as featured, 
  tbl_restaurant.min_order_amount,  
  tbl_restaurant.latitude as latitude,  
  tbl_restaurant.logo, 
  tbl_favourite_restaurant.id as is_fav, 
  tbl_restaurant.address as address,  
  IF(tbl_restaurant_timing.start_time <= '19:56:26' && tbl_restaurant.service = 'Available' && tbl_restaurant_timing.end_time >= '19:56:26', 'Open', 'Closed') AS availblity,
  tbl_restaurant.longitude as longitude,  
  (
    SELECT ROUND(AVG(tbl_rate_review.rate)) 
    FROM tbl_rate_review 
    where tbl_rate_review.restaurant_id = tbl_restaurant.id 
    GROUP BY restaurant_id
  ) as avgrating, 
  (
    SELECT ROUND(AVG(tbl_rate_review.rate), 2) 
    FROM tbl_rate_review 
    where tbl_rate_review.restaurant_id = tbl_restaurant.id 
    GROUP BY restaurant_id
  ) as rating,  
  111.045 * DEGREES(ACOS(COS(RADIANS(23.0266941)) * COS(RADIANS(latitude)) * COS(RADIANS(longitude) - RADIANS(72.6008731)) + SIN(RADIANS(23.0266941)) * SIN(RADIANS(latitude)))) AS distance_in_km 
FROM tbl_restaurant 
LEFT JOIN tbl_restaurant_featured_history ON tbl_restaurant_featured_history.restaurant_id = tbl_restaurant.id 
LEFT JOIN tbl_restaurant_menu ON tbl_restaurant_menu.restaurant_id = tbl_restaurant.id AND tbl_restaurant_menu.status='Active' 
LEFT JOIN tbl_favourite_restaurant ON tbl_favourite_restaurant.restaurant_id=tbl_restaurant.id AND tbl_favourite_restaurant.user_id=19 
LEFT JOIN tbl_restaurant_timing ON tbl_restaurant_timing.restaurant_id = tbl_restaurant.id AND tbl_restaurant_timing.day = 'Saturday' 
WHERE tbl_restaurant.status = 'Active'  
HAVING distance_in_km <= 10  
ORDER BY availblity DESC, distance_in_km ASC LIMIT 10, 10

And the output of this query

Please make the effort to properly format your query. There is a button in the formatting bar to format text as code. — GMB, Aug 01 '20 at 14:41
[DISTINCT is not a function.](https://stackoverflow.com/a/7250654/20860) — Bill Karwin, Aug 01 '20 at 14:46

score 1 · Answer 1 · answered Aug 01 '20 at 14:43

The query is poorly formated and hence rather hard to follow.

I can see this in the select clause:

tbl_restaurant_featured_history.id as featured

The where clause of a query just can't refer to an alias defined in the select clause. If you want to filter on this, then you need to use the column name (tbl_restaurant_featured_history.id) rather than the alias (featured):

where tbl_restaurant_featured_history.id is not null

score 0 · Answer 2 · answered Aug 01 '20 at 15:01

The table tbl_restaurant_featured_history is LEFT joined to the table tbl_restaurant and this is why you get nulls in the results because some rows do not match the conditions of the ON clause that you have set.
If you want to add the condition:

tbl_restaurant_featured_history.id is not null

this means that you want only the matching rows and from your sample data I see that there is only 1 matching row.
In this case all you have to do is change the join to an INNER join:

.................................
FROM tbl_restaurant 
INNER JOIN tbl_restaurant_featured_history ON tbl_restaurant_featured_history.restaurant_id = tbl_restaurant.id
.................................

How to add condition on virtual column in mysql?

2 Answers2