Proximty search stopped working after updating to CraftCMS 4.9.x

[sjeng]

We are on 4.5.2 of the plugin, and CraftCMS 4.9.x.

Since upgrading to CraftCMS 4.9.x, proximity search no longer works.

ElementQueries still returns the calculated distance for each entry, but it looks like the ORDER BY distance clause in SQL gets dropped in the generated query.

I am attaching 2 sql queries generated by the different CraftCMS versions, and you can see the difference in the ORDER BY clause at the very end of the statements.

craftcms-4.8.6-sql.txt craftcms-4.9.2-sql.txt

[lindseydiloreto]

I just ran some tests locally, and everything appears to be working normally:

• Craft 4.9.2
• Google Maps 4.5.2
• MariaDB 10.3.39

Are you using MariaDB, MySQL or Postgres? Which version are you running?

Would you mind sharing your Twig or PHP query code?

This snippet seems to be working as expected:

{% set entries = craft.entries
    .section('locations')
    .address(options)
    .orderBy('distance')
    .all() %}

It produced the expected SQL query:

SELECT `elements`.`id`, `elements`.`canonicalId`, `elements`.`fieldLayoutId`, `elements`.`uid`, `elements`.`enabled`, `elements`.`archived`, `elements`.`dateLastMerged`, `elements`.`dateCreated`, `elements`.`dateUpdated`, `elements_sites`.`id` AS `siteSettingsId`, `elements_sites`.`slug`, `elements_sites`.`siteId`, `elements_sites`.`uri`, `elements_sites`.`enabled` AS `enabledForSite`, `entries`.`sectionId`, `entries`.`typeId`, `entries`.`authorId`, `entries`.`postDate`, `entries`.`expiryDate`, `content`.`id` AS `contentId`, `content`.`title`, `subquery`.`distance` AS `address`, `content`.`field_antennaRange_febijrwe`, `content`.`field_color_hqwwlgnh`, `content`.`field_myRadius_inaygsud`, `content`.`field_ordinaryField_oxkrerln`, `content`.`field_radius_ivirzkwn`, `content`.`field_testTable_fawureeh`, `structureelements`.`root`, `structureelements`.`lft`, `structureelements`.`rgt`, `structureelements`.`level`
FROM (SELECT `elements`.`id` AS `elementsId`, `elements_sites`.`id` AS `elementsSitesId`, `content`.`id` AS `contentId`, (
            3959 * acos(
                cos(radians(34.0549076)) *
                cos(radians(`addresses`.`lat`)) *
                cos(radians(`addresses`.`lng`) - radians(-118.242643)) +
                sin(radians(34.0549076)) *
                sin(radians(`addresses`.`lat`))
            )
        ) AS `distance`
FROM `elements` `elements`
INNER JOIN `entries` `entries` ON `entries`.`id` = `elements`.`id`
INNER JOIN `elements_sites` `elements_sites` ON `elements_sites`.`elementId` = `elements`.`id`
INNER JOIN `content` `content` ON `content`.`elementId` = `elements`.`id`
LEFT JOIN `googlemaps_addresses` `addresses` ON `addresses`.`elementId` = `elements`.`id` AND `addresses`.`fieldId` = '1'
LEFT JOIN `structureelements` `structureelements` ON (`structureelements`.`elementId` = `elements`.`id`) AND (`structureelements`.`structureId`=1)
WHERE (`entries`.`sectionId`=2) AND (`addresses`.`fieldId` = '1') AND (((`elements`.`enabled`=TRUE) AND (`elements_sites`.`enabled`=TRUE)) AND (`entries`.`postDate` <= '2024-05-10 19:28:59') AND ((`entries`.`expiryDate` IS NULL) OR (`entries`.`expiryDate` > '2024-05-10 19:28:59'))) AND (`elements`.`archived`=FALSE) AND (`elements`.`dateDeleted` IS NULL) AND (`elements`.`draftId` IS NULL) AND (`elements`.`revisionId` IS NULL)
HAVING `distance` <= 500
ORDER BY `distance`) `subquery`
INNER JOIN `elements` `elements` ON `elements`.`id` = `subquery`.`elementsId`
INNER JOIN `elements_sites` `elements_sites` ON `elements_sites`.`id` = `subquery`.`elementsSitesId`
INNER JOIN `entries` `entries` ON `entries`.`id` = `subquery`.`elementsId`
INNER JOIN `content` `content` ON `content`.`id` = `subquery`.`contentId`
LEFT JOIN `structureelements` `structureelements` ON (`structureelements`.`elementId` = `subquery`.`elementsId`) AND (`structureelements`.`structureId`=1)
ORDER BY `distance`

[sjeng]

Hi! Thanks for responding!

Unfortunately the misbehaving query in our twig template is a bit complicated. Let me try to simplify the query by stripping away some of the complexity and debug a bit more. I will update this issue once i have more info.

[sjeng]

It looks like the root cause may be a bug within CraftCMS itself, but it is triggered by our own code, so not really something that needs to be fixed by your plugin. Sorry about wasting your time!

https://github.com/craftcms/cms/issues/15001

[lindseydiloreto]

No problem, glad you sorted it out! 👍

[lindseydiloreto]

For anyone still bumping into this, the solution is to update Craft itself. There was a bug in Craft which has since been fixed.