[Resolved] Generated Toolset SQL query is incorrect when searching by address

[Author]

Posts

[Focus on the Family]

Steps to reproduce

1. Visit the URL we reported
2. Enter a ZIP code of 74137, leaving the distance at 25 miles.
3. Click Search.
4. On the results page, click “Specialties” and check the box for “Sexual Addiction/Pornography” (you will need to scroll to find it).
5. Verify you see “Sexual Addition/Pornography” above the search pill.
6. Click Search.
7. The results returned will be 24 counselors, most of which are not located within 25 miles of ZIP Code 74137. The correct result should be only 1 counselor.

Toolset View Settings
From the screenshot below, you can see the distance filter is set to compare the Address field. You can see there are some additional filters, too. (SEE ATTACHED IMAGE CAPTURE 1.PNG )

The next screen shot shows the Address custom field. (SEE ATTACHED IMAGE CAPTURE 1.PNG )

Analysis of problem and fix

Looking at the Developer Tools Console in a browser from the results at step 7 above, you can see the generated SQL query that is used to obtain the results. The generated query is:

SELECT
wp_posts.*
FROM
wp_posts
LEFT JOIN wp_term_relationships ON (
wp_posts.ID = wp_term_relationships.object_id
)
INNER JOIN wp_postmeta ON (
wp_posts.ID = wp_postmeta.post_id
)
INNER JOIN wp_postmeta AS tmapsmeta ON (wp_posts.ID = tmapsmeta.post_id)
LEFT JOIN wp_toolset_maps_address_cache ON wp_toolset_maps_address_cache.address_passed = tmapsmeta.meta_value
WHERE
1 = 1
AND (
wp_term_relationships.term_taxonomy_id IN (747)
)
AND (
wp_postmeta.meta_key = 'wpcf-address'
)
AND wp_posts.post_type = 'counselor'
AND (
(wp_posts.post_status = 'publish')
)
AND ST_Distance_Sphere(
ST_PointFromText('POINT(-95.930404 36.019042)'),
wp_toolset_maps_address_cache.point
) < 40233.6
GROUP BY
wp_posts.ID
ORDER BY
ST_Distance_Sphere(
ST_PointFromText('POINT(-95.930404 36.019042)'),
wp_toolset_maps_address_cache.point
) ASC

The problem is highlighted above in red (SEE ATTACHED IMAGE CAPTURE 2.PNG). The use of “AND wp_postmeta.meta_key = ‘wpcf-address’” does not affect the results as desired. Instead, that should be part of the LEFT JOIN for wp_toolset_maps_address_cache. Like so:

SELECT
wp_posts.*
FROM
wp_posts
LEFT JOIN wp_term_relationships ON (
wp_posts.ID = wp_term_relationships.object_id
)
INNER JOIN wp_postmeta ON (
wp_posts.ID = wp_postmeta.post_id
)
INNER JOIN wp_postmeta AS tmapsmeta ON (wp_posts.ID = tmapsmeta.post_id)
LEFT JOIN wp_toolset_maps_address_cache ON wp_toolset_maps_address_cache.address_passed = tmapsmeta.meta_value
AND tmapsmeta.meta_key = 'wpcf-address'
WHERE
1 = 1
AND (
wp_term_relationships.term_taxonomy_id IN (747)
)
AND wp_posts.post_type = 'counselor'
AND (
(wp_posts.post_status = 'publish')
)
AND ST_Distance_Sphere(
ST_PointFromText('POINT(-95.930404 36.019042)'),
wp_toolset_maps_address_cache.point
) < 40233.6
GROUP BY
wp_posts.ID
ORDER BY
ST_Distance_Sphere(
ST_PointFromText('POINT(-95.930404 36.019042)'),
wp_toolset_maps_address_cache.point
) ASC

Again, the new location is highlighted in red (SEE ATTACHED IMAGE CAPTURE 2.PNG). Please note it must use “tmapsmeta” instead of wp_postmeta. This will correctly limit the join to consider only those rows of wp_postmeta where meta_key is ‘wpcf-address’. The original query seems to be fine if no other value for wp_postmeta.meta_value matches something in the address cache. However, we have several fields that frequently match in the address cache. The revised query ensures proper results.

[Nigel]

Hi there

Actually, I think you may be affected by an issue that has appeared for a small number of clients that we have a fix for in the upcoming release of Maps (we are just awaiting clearance from our systems team to release the plugin updates after testing completes).

I can check on your site if that is the case if you like. (I would install the plugin WP Data Access to be able to run some simple SELECT queries directly on the database, but wouldn't make any changes.)

Let me set up a private reply to get credentials from you if you want me to do that.

[Nigel]

Thanks for that.

I've been looking into the problem, and its not the same as the other sites I mentioned.

The existing SQL query works, inasmuch as it does calculate the post distances and only returns those within a 40km (25 mile) radius, but the calculated distances appear off.

That may be because of problems with the cached addresses and the stored coordinates, I'm not sure. I've taken a copy of your site to install locally so that I can look into the problem further without affecting your own site.

I may need some input from the developer who worked on this problem recently, so please bear with me as I investigate further.

[Focus on the Family]

Thanks for looking into this more. I had thought maybe the distance calculation was somehow incorrect, too, but that seemed unlikely, since that would surely have been noticed long ago. So I decided to see why the wrong results were picked. I modified the query slightly so I could see the address that was being matched (the modification are the addition of the 4 fields wp_posts.ID,
wp_posts.post_title, wp_toolset_maps_address_cache.address_passed, wp_toolset_maps_address_cache.point following SELECT). The modified query is:

SELECT
wp_posts.ID,
wp_posts.post_title,
wp_toolset_maps_address_cache.address_passed,
wp_toolset_maps_address_cache.point
FROM
wp_posts
LEFT JOIN wp_term_relationships ON (
wp_posts.ID = wp_term_relationships.object_id
)
INNER JOIN wp_postmeta ON (
wp_posts.ID = wp_postmeta.post_id
)
INNER JOIN wp_postmeta AS tmapsmeta ON (wp_posts.ID = tmapsmeta.post_id)
LEFT JOIN wp_toolset_maps_address_cache ON wp_toolset_maps_address_cache.address_passed = tmapsmeta.meta_value
WHERE
1 = 1
AND (
wp_term_relationships.term_taxonomy_id IN (747)
)
AND (
wp_postmeta.meta_key = 'wpcf-address'
)
AND wp_posts.post_type = 'counselor'
AND (
(wp_posts.post_status = 'publish')
)
AND ST_Distance_Sphere(
ST_PointFromText('POINT(-95.930404 36.019042)'),
wp_toolset_maps_address_cache.point
) < 40233.6
GROUP BY
wp_posts.ID
ORDER BY
ST_Distance_Sphere(
ST_PointFromText('POINT(-95.930404 36.019042)'),
wp_toolset_maps_address_cache.point
) ASC

Note that this only changes what's being returned by the query, and doesn't change which posts are returned. Running that query, you can see the "address_passed" for each of the incorrect results is "MA, LPC", "singh", or "marsh". Also note that the points returned for those values are all close to the search point. That is, the distance check is probably correct. Those values didn't seem likely to be addresses in the address fields, so I picked the first wrong result, with an ID of 12818 (you can pick any of them). And ran the following query:

SELECT * FROM wp_postmeta WHERE post_id='12818'

Looking at the results, I could see that the only row for which meta_value is "MA, LPC" (which matches "address_passed" in the query above) is for "wpcf-suffix". The "wpcf-address" has an expected address, and there's only one for that counselor. Obviously, the query was not limiting the search for address_passed to the "wpcf-address" field as expected. That's when I reformatted the query (using hidden link) to make it easier to read. That's when I understood how the query was incorrect, and what the correct query would be. The query needs to check for meta_key = "wpcf-address" when doing the JOIN for wp_toolset_maps_address_cache; otherwise it just matches any meta_value. This can be achieved with MySQL with my suggested query, or by simply changing "wp_postmeta.meta_key" to "tmapsmeta.meta_key". Either works, but I think moving it next to the join statement just looks nicer. 🙂

I do realize that the query is built dynamically, and that it's probably not built as straight SQL, so I understand that translating the above analysis into the actual code and then into a fix is probably not as simple as my wording above might suggest. Thanks again!

[Nigel]

It's lucky that you reported this, as I was able to identify a problem with the pre-release version of the Maps update which hadn't otherwise shown up in testing.

I've shared all the details with the developers so that they can make the necessary changes.

In the meantime I've tweaked the code myself and uploaded a patched version of the plugin on your site, where you can hopefully see it now working correctly.

When the plugin updates are released that patched version will be redundant (although the final solution applied by the devs may not be exactly the same).

Thanks again.

[Focus on the Family]

Great work Nigel--this appears to have resolved our problem in production too! When will the patch you mentioned be released--in the next or a later release. If not in the next our code will regress the fix we just put in place.

[Nigel]

We have a slew of plugin updates due for release, but a couple of things have come up in testing which need addressing, including this. We'll be fixing this before we go ahead with the updates, so the patch will become redundant.