Database query for getting the closest destinations in Drupal 8

Drupal 8, database, mysql 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
<?php
  // Database service. It's better to use dependency injection in your class.
  $database = \Drupal::service('database');
  $earth_radius = 3959;
  // Coordinates of the place from which you would like to get nearest places.
  $lat = '52.486081';
  $lon = '13.404616';
  // How many nearest places we would like to show?
  $limit = 10;
  // Radius to search.
  $radius = 100;
  $query = $database->select('my_table_name_from_db', 'destination');
  $query->fields('destination');
  $query->orderBy('distance');
  $query->range(0, $limit);
  // field_latitude and field_longitude - columns names from 'my_table_name_from_db'
  // where coordinates stored.
  $expr = "(
    $earth_radius * acos(
      cos(radians(:lat))
      * cos(radians(field_latitude))
      * cos(radians(field_longitude) - radians(:lon))
      + sin(radians(:lat))
      * sin(radians(field_latitude))
    )
  )";
  $query->addExpression($expr, 'distance', array(':lat' => $lat, ':lon' => $lon));
  $query->having('distance <= :radius', array(':radius' => $radius));
  $destinations = $query->execute()->fetchAll();
Share Comments