[SOLVED] How to implement Haversine Formula into Shopware 6 DAL?

How to implement Haversine Formula into Shopware 6 DAL?

I have a custom Shopware 6 Entity called Location with lat and lng fields which are storing coordinates. I want to use the Shopware DAL since the docs say that you should always use DAL for Store API routes and I am trying to do that in a Store API route. Is there any way with the regular Shopware DAL to implement the Haversine formula?

This is the formula in plain SQL:

  SELECT id, ( 6371 * acos( cos( radians(input_latitude) ) * cos( radians( 
latitude ) ) 
* cos( radians( longitude ) - radians(100.56133310918271) ) + sin( radians(input_longitude) ) * sin(radians(input_latitude)) ) ) AS distance 
FROM location 
HAVING distance < 1 
ORDER BY distance 
LIMIT 0 , 500;

Solution

As already stated in the comments to your question, doing complex computations like this with the DAL can't be done without altering some of its inner workings. That's just the nature of pretty much any abstraction layer.

While it is true that it is recommended to use the DAL in any case possible, I think it's absolutely fair game to use "raw" SQL when the limits of the abstraction layer have been reached. When you persist data using the DAL, events are emitted that allow the system and third-party developers to react to changes. That is one of the main aspects of why using the DAL is recommended. While reading data using the DAL will also emit events, they're not as critical to the overall architecture comparatively.

I would recommend trying to pre-select the ids and distances of your table using a plain SQL query. Try to keep it as simple and performant as possible. Then use the pre-selected ids with the DAL repository of your entity and fetch the full data sets like that. You might also want to add extensions to the entity instances to enrich them with the distance, in case you might need it. Then at least the data loading events for the actual entities will still be dispatched.

$idDistances = $this->connection->fetchAllAssociative(
    'SELECT LOWER(HEX(id)) AS id, (...) AS distance FROM ...'
);

$ids = array_column($idDistances, 'id');
$entities = $this->repository->search(new Criteria($ids), $context)->getEntities();

$sortedEntities = [];
foreach ($idDistances as $idDistance) {
    [$id, $distance] = $idDistance;
    $entity = $entities->get($id);

    if (!$entity) {
        continue;
    }

    $textStruct = new TextStruct();
    $textStruct->setContent($distance);
    $entity->addExtension('distance', $textStruct);

    $sortedEntities[] = $entity;
}