Basically I have to get the algorithm to show restaurants in the selected zone and in other zones which actually makes sense..
E.g: if my area is in North of some city, I would like to show up restaurants in north, then move to central and then to east and then to west and finally to the extreme opposite i.e., south
In case of east, then central, north, south, finally extreme opposite i.e., west
I have in my db the following
zones table with ids, 1 - North, 2 - East, 3 - West, 4 - South, 5 - Central.
And localities table with locality/area of each city and structure goes as
locality_id | locality_name | zone_id(FK)
I have my model (php/codeigniter)
$this->db->select('menu_item.restaurant_id, menu_item.price, localities.locality_name, restaurant_information.restaurant_name, restaurant_information.restaurant_address, restaurant_information.is_halal, restaurant_information.cuisine, restaurant_information.city, restaurant_information.pincode');
$this->db->from('menu_item');
$this->db->where('menu_item.dish_id', $dish_id);
$this->db->where('menu_item.is_active', 1);
$this->db->where('restaurant_information.is_active', 1);
$this->db->join('restaurant_information', 'menu_item.restaurant_id = restaurant_information.restaurant_id');
$this->db->join('localities', 'restaurant_information.locality_id = localities.locality_id');
Its okay if I have too many joins or whatever.. but definitely not with lat/long or google geo..
Please help me out.. I tried order_by_field.. Its okay and it works but I am not able to give it dynamically..
Is there any solution or am I headed in the wrong direction..? Correct me if I got the structure wrong..!
And I am also ready if the order by part could be done on the result object where I can fetch the result and sort it based on the location.. but I prefer MySQL to do the job. Thanks in advance