0

I'm new to this forum, sorry in advance :) I have a problem with my display of my posts Let me explain ^^

I want to perform an orderby => '$distance' and display the posts accordingly. $lat2 and $lon2 are listed in wp.postmeta in meta_key and meta_value. Also $lat and $lon are well recovered without worrie.

I tried to put meta_key/value in my query, but it doesn't work because my calculation is not done in my database.

object(WP_Query)[9785]
  public 'query' => 
    array (size=5)
      'post_type' => string 'post' (length=4)
      'lon' => string '43.282' (length=6)
      'lat' => string '5.405' (length=5)
      'orderby' => float 4844
      'order' => string 'asc' (length=3)

I have a city filter which when clicked launches my function. I retrieve the radius that I set to 20.30 or 50 depending on the user's select option

I created a post-view.php template which retrieves the found post well, my template is a loop if $rayon<50 and there are posts it shows me the template several times :)

function filter_city(){

    $rayon = $_POST['rayon'];
    $lon = htmlspecialchars($_POST["lon"]);
    $lat = htmlspecialchars($_POST["lat"]);
    $lat2 = get_lat(get_the_id());
    $lon2 = get_lon(get_the_id());
    $resultat = Misc::distance($lat, $lon, $lat2, $lon2, $miles = false);

    $m = round($resultat);

    $myposts = new WP_Query([
        'post_type' => 'post',
        'lon' => $lon,
        'lat' => $lat,
        'orderby' => $m,
        'order' => 'asc'

    ]);
    $response = '';

    if ($myposts->have_posts()) {
        while ($myposts->have_posts()) : $myposts->the_post();
            include('post-view.php');

        endwhile;
    } else {
        $response .= include('false.php');
    }

I've tried everything

Thank you in advance for your help

1 Answer 1

0

This may not be possible using what WP_Query gives you out of the box, it wasn't designed to do math across multiple parameters for sorting. You can filter via meta_query and provide bounds, and you can order by a single value, you can even order by multiple values, e.g. show all items by their lat, and use long if they have the same lat, but WP_Query can't do the distance calculation.

To do this, you will need to construct a raw SQL to retrieve the post IDs, then pass those IDs into a WP_Query to fetch the posts your SQL query found.

Additionally, if it could, the SQL query would be very very slow. For this reason special table and column formats are generally used for long/lat if you want to be able to pass an arbitrary parameter for sorting. Most people solve this problem by doing the ordering in the browser, as this is usually accompanied by a map of sorts. Fundamentally what you want is very expensive/slow to produce and you've ran into a tradeoff of accuracy vs speed.

5
  • Thank you very much for your explanations. How do you think I should proceed? How can I do this by doing the command in the browser? Thanks for your help Aug 2 at 11:53
  • Is it possible in your opinion to use leaflet and scroll over it and that will update my post list: 1) I put my city in my input 2) A zoom on leaflet is done 3) My list of wp posts is updating (displays posts in the same radius) Aug 2 at 13:27
  • Look at it this way, if I asked you for all the shops within a 50km radius, would you go through your entire list doing the calculation? Probably not. If I gave you my location and you noticed it was in the USA, would you skip the Canadian entries? If I told you I was at 41 by 43, wouldn't it make sense to only look at the 40-45 entries then only do the math on those? And if you have leaflet then isn't Leaflet doing that math for you in order to display them? You don't have to give ultra precise results, you just need to sort them into buckets and return the relevant buckets
    – Tom J Nowell
    Aug 2 at 15:33
  • likewise, if you're on an ecommerce site, do you see textboxes for prices ranges where you can enter that you want items between $21.79 and $32.40, or do you see $20-$30 ranges? Towns/States/Countries/Continents rather than streets etc. Your server doesn't have to return exactly what the user wanted, javascript can filter out a coarse best set of results.
    – Tom J Nowell
    Aug 2 at 15:35
  • At a minimum, a better but less efficient process is to filter your lat long to a square, then perform the exact distance calculation locally and sort that way, e.g. all the lats between 40 and 40.5, and all the longs between 39 and 39.5. If you can pre-group them in a taxonomy via hooks on save/publish it'll be hundreds/thousands of times faster. Sure it'll pick up things in the corners that don't match but doing math in a database query is slow and expensive, especially with post meta. Doing it in javascript is fast and in realtime
    – Tom J Nowell
    Aug 2 at 15:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.