SQL error with proximity search

[jedimonkey]

We have a project that is about to go live, and we have it working using the Smart Maps. Just noticed it's discontinuation so trying to swap our logic over to this plugin. However, we have a weird bug that I can't seem to determine how it's occurring.

We have a section with an address field on it, and trying to perform a proximity search on it. As soon as the element query is executed we have an SQL error:

SQLSTATE[42712]: Duplicate alias: 7 ERROR: table name "addresses" specified more than once
The SQL being executed was: SELECT COUNT(*)
FROM (SELECT "elements"."id" AS "elementsId", "elements_sites"."id" AS "elementsSitesId", "content"."id" AS "contentId", (
3959 * acos(
cos(radians(-31.9523123)) *
cos(radians("addresses"."lat")) *
cos(radians("addresses"."lng") - radians(115.861309)) +
sin(radians(-31.9523123)) *
sin(radians("addresses"."lat"))
)
) AS "distance", (
3959 * acos(
cos(radians(-31.9523123)) *
cos(radians("addresses"."lat")) *
cos(radians("addresses"."lng") - radians(115.861309)) +
sin(radians(-31.9523123)) *
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" = 6
LEFT JOIN "googlemaps_addresses" "addresses" ON "addresses"."elementId" = "elements"."id" AND "addresses"."fieldId" = 6
WHERE ("entries"."sectionId"=4) AND ("addresses"."fieldId" = 6) AND ("addresses"."fieldId" = 6) AND ("elements"."archived"=FALSE) AND ((("elements"."enabled"=TRUE) AND ("elements_sites"."enabled"=TRUE)) AND ("entries"."postDate" <= '2021-03-05 03:11:00') AND (("entries"."expiryDate" IS NULL) OR ("entries"."expiryDate" > '2021-03-05 03:11:00'))) AND ("elements"."dateDeleted" IS NULL) AND ("elements"."draftId" IS NULL) AND ("elements"."revisionId" IS NULL)) "subquery"
INNER JOIN "entries" "entries" ON "entries"."id" = "subquery"."elementsId"
INNER JOIN "elements" "elements" ON "elements"."id" = "subquery"."elementsId"
INNER JOIN "elements_sites" "elements_sites" ON "elements_sites"."id" = "subquery"."elementsSitesId"
INNER JOIN "content" "content" ON "content"."id" = "subquery"."contentId"
WHERE ("distance" <= 500) AND ("distance" <= 500)

The twig to generate it is pretty simple: {% dd([craft.entries.section('centre').address({ target: {lat: -31.9523123, lng: 115.861309} }) | length]) %}

The issue is clearly the double left join:

LEFT JOIN "googlemaps_addresses" "addresses" ON "addresses"."elementId" = "elements"."id" AND "addresses"."fieldId" = 6
LEFT JOIN "googlemaps_addresses" "addresses" ON "addresses"."elementId" = "elements"."id" AND "addresses"."fieldId" = 6

I've traced the code and can't determine why it's calling AddressField->modifyElementsQuery twice, which calls the ProximitySearchHelper::modifyElementsQuery. It either needs a check to determine it's being added already, or work out why the duplicate request is occurring.

We are using Postgres, so maybe there is a bug with the postgres side of things?

Here is a dump of the Entry Query which to me looks okay:

[
    0 => craft\elements\db\EntryQuery#1
    (
        [editable] => false
        [sectionId] => [
            0 => 4
        ]
        [typeId] => null
        [authorId] => null
        [authorGroupId] => null
        [postDate] => null
        [before] => null
        [after] => null
        [expiryDate] => null
        [*:defaultOrderBy] => [
            'entries.postDate' => 3
        ]
        [elementType] => 'craft\\elements\\Entry'
        [query] => null
        [subQuery] => null
        [contentTable] => '{{%content}}'
        [customFields] => null
        [inReverse] => false
        [asArray] => false
        [ignorePlaceholders] => false
        [drafts] => false
        [draftId] => null
        [draftOf] => null
        [draftCreator] => null
        [savedDraftsOnly] => false
        [revisions] => false
        [revisionId] => null
        [revisionOf] => null
        [revisionCreator] => null
        [id] => null
        [uid] => null
        [fixedOrder] => false
        [status] => [
            0 => 'live'
        ]
        [archived] => false
        [trashed] => false
        [dateCreated] => null
        [dateUpdated] => null
        [siteId] => null
        [unique] => false
        [preferSites] => false
        [enabledForSite] => false
        [leaves] => false
        [relatedTo] => null
        [title] => null
        [slug] => null
        [uri] => null
        [search] => null
        [ref] => null
        [with] => null
        [orderBy] => ''
        [withStructure] => false
        [structureId] => null
        [level] => null
        [hasDescendants] => null
        [ancestorOf] => null
        [ancestorDist] => null
        [descendantOf] => null
        [descendantDist] => null
        [siblingOf] => null
        [prevSiblingOf] => null
        [nextSiblingOf] => null
        [positionedBefore] => null
        [positionedAfter] => null
        [craft\elements\db\ElementQuery:_placeholderCondition] => null
        [craft\elements\db\ElementQuery:_placeholderSiteIds] => null
        [craft\elements\db\ElementQuery:_result] => null
        [craft\elements\db\ElementQuery:_resultCriteria] => null
        [craft\elements\db\ElementQuery:_searchScores] => null
        [select] => [
            '**' => '**'
        ]
        [selectOption] => null
        [distinct] => null
        [from] => null
        [groupBy] => null
        [join] => null
        [having] => null
        [union] => null
        [withQueries] => null
        [params] => []
        [queryCacheDuration] => null
        [queryCacheDependency] => null
        [yii\base\Component:_events] => []
        [yii\base\Component:_eventWildcards] => []
        [yii\base\Component:_behaviors] => [
            'customFields' => craft\behaviors\CustomFieldBehavior#2
            (
                [hasMethods] => true
                [socialLinks] => null
                [adminLinks] => null
                [newsletterSignupForm] => null
                [accountName] => null
                [service] => null
                [contactDetails] => null
                [slides] => null
                [actionLabel] => null
                [caption] => null
                [action] => null
                [phone] => null
                [blocks] => null
                [description] => null
                [email] => null
                [location] => null
                [richContent] => null
                [image] => null
                [page] => null
                [heading] => null
                [blurb] => null
                [links] => null
                [copy] => null
                [desktopAlignment] => null
                [mobileAlignment] => null
                [verticalAlignment] => null
                [inline] => null
                [video] => null
                [autoplay] => null
                [controls] => null
                [fullscreen] => null
                [icon] => null
                [duration] => null
                [form] => null
                [formTitle] => null
                [testimonial] => null
                [attribution] => null
                [postalAddress] => null
                [headline] => null
                [centre] => null
                [coaches] => null
                [programs] => null
                [gender] => null
                [age] => null
                [columns] => null
                [quote] => null
                [logo] => null
                [altPhone] => null
                [fax] => null
                [endDate] => null
                [newsTags] => null
                [overview] => null
                [event] => null
                [hero] => null
                [seo] => null
                [courseCode] => null
                [documents] => null
                [mode] => null
                [redirect] => null
                [row] => null
                [code] => null
                [destination] => null
                [themeColour] => null
                [regional] => null
                [imageSets] => null
                [backgroundColour] => null
                [qualifications] => null
                [activity] => null
                [customOrder] => null
                [popular] => null
                [featuredNews] => null
                [profilePhoto] => null
                [cost] => null
                [bookingUrl] => null
                [website] => null
                [fallbackNewsImages] => null
                [fallbackHeroImages] => null
                [fallbackStoryImages] => null
                [recreations] => null
                [temp] => null
                [training] => null
                [activityTitle] => null
                [hasSubheadings] => null
                [theme] => null
                [callToAction] => null
                [label] => null
                [relatedActivities] => null
                [address] => [
                    'target' => [
                        'lat' => -31.9523123
                        'lng' => 115.861309
                    ]
                ]
                [craft\behaviors\CustomFieldBehavior:_customFieldValues] => []
                [owner] => craft\elements\db\EntryQuery#1(...)
                [yii\base\Behavior:_attachedEvents] => []
            )
        ]
        [where] => null
        [limit] => null
        [offset] => null
        [indexBy] => null
        [emulateExecution] => false
    )
]

[jedimonkey]

oh, we are using the latest version - 4.0.2. Here is a listing of other software versions:

Application Info PHP version 7.4.14 OS version Linux 4.19.121-linuxkit Database driver & version PostgreSQL 13.1 Image driver & version Imagick 3.4.4 (ImageMagick 6.9.10-23) Craft edition & version Craft Pro 3.6.8 Yii version 2.0.40 Twig version v2.14.3 Guzzle version 7.2.0 Plugins Colour Swatches 1.4.1.1 Embedded Assets 2.5.1 Feed Me 4.3.5.1 Freeform 3.10.8 Google Maps 4.0.2 Mix 1.5.2 Navigation 1.4.14 Plural 2.0.0 Query 2.0.3 Redactor 2.8.5 Redactor Anchors 1.1.0 Redactor Custom Styles 3.0.4 SEO 3.6.7 Smith 1.1.12 Super Table 2.6.7 Timetable 3.2.1 Validateit 1.0.3

[jedimonkey]

Aha! worked it out. We originally had a supertable with an smartaddress field in it. But we found that we couldn't use the proximity searching functionality with it nested in the supertable, so it was added as it's own seperate field. As the client had already started some data entry, we held off removing it. So there lies the issue the second field nested in the supertable, I've rolled everything back, removed the superfluous field from the supertable and performed the upgrade to googlemaps. No longer having that sql error.

[lindseydiloreto]

Ok cool, glad you got it sorted out! 👍