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!
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. **
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.
Ok Jamal. Thanks for your time!
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.
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!
One more thing. The order in mobile is different than desktop where is ordered by distance.
How can is this possible?
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?
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!
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.
Hello Jamal
Are you installed plugins in the website?
Thanks!
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.
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