How to extend given query for search by miles in Drupal 8?

Question

I have given query for node and want to extend search by miles,

$query = \Drupal::entityQuery('node')
        ->condition('status', 1)
        ->condition('type', 'event')
        ->range(0, 10);

Location coming in array format using following query (installed geolocation module) and want to record using custom query not using internal search feature

$locationInArray = $node->get('location')->getValue();

Array value are like:

lat (latitude), lng (longitude), lat_sin (precalculated latitude sine), lat_cos (precalculated latitude cosine), lng_rad (precalculated radian longitude).

I am facing problem on lat long coming from another table so how to extend above query

I need distance wise search like 2 km/miles without help of drupal module because i have already implemented that but not fulfill my all needs so want to make custom search module.

here is given distance query but i am new in drupal so please help me to integrate given query in drupal my code.

SELECT id, ( 3959 * acos ( cos ( radians(78.3232) ) * cos( radians( lat ) ) * cos( radians( lng ) - radians(65.3234) ) + sin ( radians(78.3232) ) * sin( radians( lat ) ) ) ) AS distance FROM markers HAVING distance < 30 ORDER BY distance LIMIT 0 , 20;

can you confirm that 'location' is not actually 'field_location' ? — zanvidmar
field_location location is coming from another table it's created from geolocation module — Ashok Kumar
yes each field if drupal has own table. I just want to be sure that $locationInArray = $node->get('location')->getValue(); is not $locationInArray = $node->get('field_location')->getValue();:) — zanvidmar

zanvidmar zanvidmar · Accepted Answer · 2019-07-17T08:36:38

Can you try with:

$query = \Drupal::entityQuery('node')
    ->condition('status', 1)
    ->condition('type', 'event')
    ->condition('location.lat', $your_value_lat)
    ->condition('location.lng', $your_value_lng)
    ->condition('location.lat_sin', $your_value_lat_sin)
    ->condition('location.lat_cos', $your_value_lat_cos)
    ->condition('location.lng_rad', $your_value_lng_rad)
    ->range(0, 10);

EDIT after question has been changed:

To use query directyl in drupal use $query = \Drupal::database()->query()

More: https://www.drupal.org/docs/8/api/database-api

Your example should look something like that (I just swap one value with variable to show how this can be done):

$query = \Drupal::database()
  ->query("SELECT id, ( :example_variable * acos ( cos ( radians(78.3232) ) * cos( radians( lat ) ) * cos( radians( lng ) - radians(65.3234) ) + sin ( radians(78.3232) ) * sin( radians( lat ) ) ) ) AS distance FROM markers HAVING distance < 30 ORDER BY distance LIMIT 0 , 20;",
[
  ':example_variable' => '3959'
]);
$result = $query->fetchAll();

How to extend given query for search by miles in Drupal 8?

1 Answers