How to implement Haversine Formula into Shopware 6 DAL?

Question

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;

I believe this is not possible in DAL directly. What I did in the past (https://stackoverflow.com/questions/70023661/pull-certain-products-to-front-by-adding-custom-sql-to-criteria/70113483#70113483) is to add custom code to the criteria builder, maybe a similar approach is possible for your case as well. Still it should be okay to also use plain SQL in some cases where DAL cannot do the job and where you do not have to support different databases. — Alex, Feb 07 '23 at 13:37
Very good question by the way, no clue why this got a close vote. — Alex, Feb 07 '23 at 13:38
Thank you, I already found your question on Google, but I hoped for a more easy solution. But I already thought it's not possible via DAL. — Abenil, Feb 07 '23 at 13:41

dneustadt · Accepted Answer · 2023-02-07T19:14:44.227

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;
}

I will test the performance implications since this is pretty critical, but this is much better than anything I came up with. Thank you so much for sharing this! — Abenil, Feb 08 '23 at 18:55

How to implement Haversine Formula into Shopware 6 DAL?

1 Answers1