How to add condition on virtual column in mysql?

5

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_restaurant3  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

enter image description here

answered question

Please make the effort to properly format your query. There is a button in the formatting bar to format text as code.

Thank you so much @GMB ! is it looking formatted now?

1 Answer

5

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

posted this

Have an answer?

JD

Please login first before posting an answer.