Smart Map plugin, HAVING SQL and GROUP BY elements.id

Question

It seems like most Craft queries contain GROUP BY elements.id but this seems to clash with a HAVING clause when using the Smart Map plugin.

Where does this come from and is it possible to override/remove this?

Answer 1

The HAVING clause comes from this piece of code:

// Modify query
$query
    ->addSelect($haversine.' AS distance')
    ->having('distance <= '.$filter->range)
;

The variables going into this query modification are:

• $haversine - A complex formula which determines the relative distance of each address from the center point.
• $filter->range - The maximum search distance. (defaults to 25)

This code exists so that your proximity search results can be sorted by closest locations. It will be triggered any time you chain this into your Twig query:

.myFieldHandle(params)

You can see the full details of how this all ties together in the documentation here:

https://plugins.doublesecretagency.com/smart-map/sorting-entries-by-closest-locations/

There should be no conflict with any existing GROUP BY clause... Smart Map does not set or modify that aspect of the query. And there is no inherent conflict between HAVING and GROUP BY, as demonstrated by this w3schools example, which utilizes both in combination.