Skip Navigation

[Resolved] View: Order by distance doesn't work

This support ticket is created 3 years, 3 months ago. There's a good chance that you are reading advice that it now obsolete.

This is the technical support forum for Toolset - a suite of plugins for developing WordPress sites without writing PHP.

Everyone can read this forum, but only Toolset clients can post in it. Toolset support works 6 days per week, 19 hours per day.

Sun Mon Tue Wed Thu Fri Sat
9:00 – 13:00 9:00 – 13:00 9:00 – 13:00 9:00 – 13:00 - - 9:00 – 13:00
14:00 – 18:00 14:00 – 18:00 14:00 – 18:00 14:00 – 18:00 - - 14:00 – 18:00

Supporter timezone: Africa/Casablanca (GMT+01:00)

This topic contains 22 replies, has 3 voices.

Last updated by Nigel 2 years, 6 months ago.

Assisted by: Jamal.

Author
Posts
#2154859
Captura de pantalla 2021-08-29 a las 17.27.16.png

Hello.

I have some view where i ordering results by distance. In That views the order by distance dont't wrok now. They were working fine till now, but now not.

U can see an example here: hidden link

Any idea to solve this?

Thanks!

#2154883

Hello and thank you for contacting Toolset support.

To better assist you with this issue, I'll need to activate the views debugging tool and check the underlying SQL query of the view. Would you allow me to log in as an administrator? Your next reply will be private to let you share credentials safely. ** Make a database backup before sharing credentials. **

#2154903

It seems that the underlying SQL query does order the results by distance. This is the SQL query:

SELECT SQL_CALC_FOUND_ROWS  wp_posts.ID FROM wp_posts  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_postmeta.meta_key = 'wpcf-adreca-del-negoci'
) AND wp_posts.post_type = 'negoci' AND ((wp_posts.post_status = 'publish' OR wp_posts.post_status = 'private')) GROUP BY wp_posts.ID ORDER BY ST_Distance_Sphere(ST_PointFromText('POINT(-8.0150528 31.6407808)'), wp_toolset_maps_address_cache.point) ASC LIMIT 0, 12

Maybe the distance shortcode does not calculate the distance correctly. Let me approach our 2nd Tier for assistance on this matter. I'll get back to you as soon as possible.

#2154907

Ok Jamal. Thanks for your time!

#2155091

Nothing

Thanks!

#2156229

Hello there! Our 2nd Tier has analyzed this issue and he explained that we have two calculations there that are performed from two different locations and that produce two different results.

The results are, indeed, ordered by distance by the database engine(MySQL or MariaDB). And that's performed by this fragment of the underlying SQL query ORDER BY ST_Distance_Sphere(ST_PointFromText(‘POINT(-8.0150528 31.6407808)’), wp_toolset_maps_address_cache.point) ASC
.

Whereas the distance, generated by the following shortcodes, is calculated by the plugin's code(PHP).

[wpv-geolocation]([toolset-maps-distance-value origin_source='visitor_location' postmeta='wpcf-adreca-del-negoci']Km)[/wpv-geolocation]

One would expect/hope that they would produce the same results, but it is possible they do not. These are two separate calculations, performed by two different engines.

This is now escalated to the developers, however, I am afraid it won't be addressed any time soon. If you can think of any workaround that will suit your use case, let us know and we'll do our best to help.

#2156711

Hello again Jamal and thanks for answer.

This was working fine 2 months ago and not now. Some change has been made compared to previous versions. Either way ... DO NOT sort by distance. If you go in to look there is no pattern in view. How can I get the results to be ordered from the smallest distance to the greatest distance from where I am located? In the view that I showed you, the distance from where I am located appears in parentheses. I can find a recor located 100 km after one located 2 km, and after this one located 20 km. However different the calculations are, the records should have a certain order, and they have no order at all.

Simply... order by distance (ascending or descending) is not working and some time ago yes.

It does not make any sense to use "order by distance from current location". I hope that toolset solves this.

Thanks!

#2157289

One more thing. The order in mobile is different than desktop where is ordered by distance.
How can is this possible?

#2162231

My apologies for the late reply, I was trying to find the issue between the two calculations and I finally realized, that the issue is most probably from the database server, or from a custom code that changes the order of posts. Every time we reload the page we get a different order. And that's why you see the difference between Desktop and mobile.

I need to take a copy of your website and debug it locally further. Would you allow me to take it?

#2162251

Of course Jamal.
U'll need google maps keys to test it in local. Remember to add wourn own keys!
Thanks a lot for your help!

#2162853

I cloned your website to our online platform, because I need SSL for user location, and I noticed that not only the results between Desktop and Mobile are different, reloading the page multiple times on desktop will also give different results. I digged down a bit and it seems that this comes from the database server that is giving different results for the same query.

I am reaching again our 2nd Tier about my findings and I'll get back to you as soon as possible.

#2171161

Hello Jamal
Are you installed plugins in the website?
Thanks!

#2171835

Hello and my apologies for the late reply. I have already taken a copy of your website. And we find out that the distance is not calculated correctly by the database engine.

We have escalated a copy of your website to our Toolset Maps developers. They could not work on it yet. I will get back to you as soon as a developer could work on it and we find out something to share.

Our sincere apologies for the inconvenience.

#2174763

OK!

#2183893

Hello again Jamal

I have this message in wordpress admin:

This is the generated shortcode, based on the settings that you have selected:

"You can now copy and paste this shortcode anywhere you want.
Is this made by you?"

Thanks