SELECT SQL_CALC_FOUND_ROWS
restaurants.cache_visits,
restaurants.id, restaurants.booking, restaurants.oo, restaurants.oo_other_restaurant, restaurants.restaurant, restaurants.restaurant_url, restaurants.picture, restaurants.picture_date, restaurants.city, restaurants.zipcode, restaurants.hb, restaurants.latitude, restaurants.longitude,
restaurants.oo_delivery_type, restaurants.oo_takeaway, restaurants.cache_gold, restaurants.cache_favorite, restaurants.cache_ads_offer,
roo.restaurant_url AS restaurant_url_other,
ro.discount_rate, ro.offer_fr, ro.offer_en, ro.description_fr, ro.description_en,
ro2.discount_rate AS discount_rate_delivery, ro2.offer_fr AS offer_fr_delivery, ro2.offer_en AS offer_en_delivery, ro2.description_fr AS description_fr_delivery, ro2.description_en AS description_en_delivery,
gcp.localite_fr, gcp.localite_en,
restaurants.cache_cuisines_fr AS cuisines
, IF( ((ads_cpc.date_end IS NOT NULL AND ads_cpc.date_start <= UNIX_TIMESTAMP() AND ads_cpc.date_end >= UNIX_TIMESTAMP()) OR (ads_cpc.date_end IS NULL AND ads_cpc.date_start <= UNIX_TIMESTAMP())) AND ads_cpc.daily_balance>=ads_cpc.cpc, ads_cpc.id, NULL) AS id_ads_cpc,
IF( ((ads_cpc.date_end IS NOT NULL AND ads_cpc.date_start <= UNIX_TIMESTAMP() AND ads_cpc.date_end >= UNIX_TIMESTAMP() ) OR (ads_cpc.date_end IS NULL AND ads_cpc.date_start <= UNIX_TIMESTAMP())) AND ads_cpc.daily_balance>=ads_cpc.cpc , ads_cpc.cpc,0) AS cpc
FROM restaurants
LEFT JOIN ads_cpc ON restaurants.id = ads_cpc.id_restaurant AND ads_cpc.status='1'
LEFT JOIN restaurants roo ON restaurants.oo_other_restaurant = roo.id
LEFT JOIN restaurants_offers ro ON restaurants.id = ro.id_restaurant AND ro.date_start <= UNIX_TIMESTAMP() AND ro.date_end >= UNIX_TIMESTAMP() AND ro.status=1 AND ro.onsite=1
LEFT JOIN restaurants_offers ro2 ON restaurants.id = ro2.id_restaurant AND ro2.date_start <= UNIX_TIMESTAMP() AND ro2.date_end >= UNIX_TIMESTAMP() AND ro2.status=1 AND ro2.onsite=0
LEFT JOIN geo_cp gcp ON restaurants.id_cp = gcp.id
LEFT JOIN restaurants_cuisines ON restaurants.id=restaurants_cuisines.id_restaurant
LEFT JOIN cuisines ON restaurants_cuisines.id_cuisine=cuisines.id
LEFT JOIN restaurants_dishes2 ON restaurants.id=restaurants_dishes2.id_restaurant
LEFT JOIN dishes2 ON restaurants_dishes2.id_dish2=dishes2.id
LEFT JOIN dishes1 ON dishes2.id_dish1=dishes1.id
WHERE 1
AND restaurants.onsite=1 AND restaurants.status=1 AND cuisines.cuisine_url_fr IN ('halal','sandwich','barbecue','snacking_fr','grecque','italien','mexicain','pakistanaise','portugaise') AND dishes1.dish1_url_fr IN ('sandwich')
GROUP BY restaurants.id
ORDER BY cpc DESC, IFNULL(IFNULL(ro.discount_rate,discount_rate_delivery),0) DESC, restaurants.subscription DESC, restaurants.rank_find DESC, restaurants.restaurant ASC LIMIT 12
Restaurants sandwich - bagel - wrap
