MySQL ORDER BY最近的区域

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

Basically, if I understand well you want to sort by the closest zone.

So maybe you should translate your ids 1, 2, 3, 4, 5 into coordinates like:

  • 1 North: x:0, y:1
  • 2 East: x:1, y:0
  • 3 West: x:-1, y:0
  • 4 South: x:0, y:-1
  • 5 Center: x:0, y:0

Then sort them by distance calculated.

Example: You are located in West and you have restaurants located in West, North, South and Central

If you have the coordinates you can calculate the distance then sort your results:

  • West restaurant distance 0
  • North restaurant distance 1.2xxx (something like that)
  • South restaurant distance 1.2xxx (something like that)
  • Central restaurant distance 1

So, assuming you have implemented the functions GET_DISTANCE() & GET_COORD() you would have

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
FROM menu_item
JOIN restaurant_information
  ON (menu_item.restaurant_id = restaurant_information.restaurant_id)
JOIN localities
  ON (restaurant_information.locality_id = localities.locality_id)
WHERE TRUE
  AND menu_item.dish_id = $dish_id
  AND menu_item.is_active = 1
  AND restaurant_information.is_active = 1
ORDER BY
  GET_DISTANCE(
    GET_COORD($my_position)
  , GET_COORD(restaurant_information.locality_id)
  )
;