0

I've built a custom query for geolocation searches. I'm saving lat and lng within each custom post type as a meta value. My query extension returns results successfully when lat and lng are the only search variables.

Problem: I would like to also add tax_queries to my query to further select posts

Class to extend query:

class WP_Query_Geo extends WP_Query {

  function __construct( $args = array() ) {

    if(!empty($args['lat'])) {

      $this->lat = $args['lat'];
      $this->lng = $args['lng'];
      $this->distance = $args['distance'];
      $this->lat_meta_name = $args[ 'lat_meta_name' ];
      $this->lng_meta_name = $args[ 'lng_meta_name' ];
      $this->orderby = $args[ 'orderby' ];
      $this->unit_of_measure = 3959;

      add_filter('posts_fields', array($this, 'posts_fields'));
      add_filter('posts_join', array($this, 'posts_join'));
      add_filter('posts_where', array($this, 'posts_where'));
      add_filter('posts_orderby', array($this, 'posts_orderby'));

    }

    parent::query($args);

    remove_filter('posts_fields', array($this, 'posts_fields'));
    remove_filter('posts_join', array($this, 'posts_join'));
    remove_filter('posts_where', array($this, 'posts_where'));
    remove_filter('posts_orderby', array($this, 'posts_orderby'));

  }

  function posts_fields($fields) {
    global $wpdb;
    $fields = $wpdb->prepare(" $wpdb->posts.*, pm1.meta_value, pm2.meta_value,
    ACOS(SIN(RADIANS(%f))*SIN(RADIANS(pm1.meta_value))+COS(RADIANS(%f))*COS(RADIANS(pm1.meta_value))*COS(RADIANS(pm2.meta_value)-RADIANS(%f))) * %d AS distance ", $this->lat, $this->lat, $this->lng, $this->unit_of_measure);
    return $fields;
  }

  function posts_join($join) {
    global $wpdb;
    $join .= " INNER JOIN $wpdb->postmeta pm1 ON ($wpdb->posts.id = pm1.post_id AND pm1.meta_key = '".$this->lat_meta_name."')";
    $join .= " INNER JOIN $wpdb->postmeta pm2 ON ($wpdb->posts.id = pm2.post_id AND pm2.meta_key = '".$this->lng_meta_name."')";
    return $join;
  }

  function posts_where($where) {
    global $wpdb;
    $where .= $wpdb->prepare(" HAVING distance < %d ", $this->distance);
    return $where;
  }

  function posts_orderby($orderby) {
    if($this->orderby == 'distance') $orderby = " distance ASC, " . $orderby;
    return $orderby;
  }

}

 

Query:

$args = array(
    'post_type'      => 'custom',
    'posts_per_page' => 15,
    'post_status'    => 'publish',
    'paged'          => $page,
    'orderby'        => 'distance',
    'lat'            => $lat,
    'lng'            => $lng,
    'distance'       => $distance,
);

$the_query = new WP_Query_Geo( $args );

 

The above works. As soon as I add a tax query I receive an error.

Example tax query:

  $tax_query_args = array( 'relation' => 'AND' );
  $tax_to_push = array(
      'taxonomy' => 'custom-tax',                
      'field'    => 'id',                    
      'terms'    => array(1,2,3),
      'operator' => 'IN'                   
  );
  array_push($tax_query_args, $tax_to_push);
  $args['tax_query'] = $tax_query_args; 

 

Error Message:

WordPress database error You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'GROUP BY wp_posts.ID ORDER BY distance ASC, wp_posts.post_date DESC LIMIT 0, 15' at line 4 for query
SELECT SQL_CALC_FOUND_ROWS wp_posts.*, pm1.meta_value, pm2.meta_value,\n ACOS(SIN(RADIANS(42.407211))*SIN(RADIANS(pm1.meta_value))+COS(RADIANS(42.407211))*COS(RADIANS(pm1.meta_value))*COS(RADIANS(pm2.meta_value)-RADIANS(-71.382437))) * 3959 AS distance
FROM wp_posts
INNER JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id)
INNER JOIN wp_postmeta pm1 ON (wp_posts.id = pm1.post_id AND pm1.meta_key = 'office_lat')
INNER JOIN wp_postmeta pm2 ON (wp_posts.id = pm2.post_id AND pm2.meta_key = 'office_lng')
WHERE 1=1
    AND ( \n wp_term_relationships.term_taxonomy_id IN (26)\n)
    AND wp_posts.post_type = 'clinicians'
    AND ((wp_posts.post_status = 'publish'))
HAVING distance < 50
GROUP BY wp_posts.ID
ORDER BY distance ASC, wp_posts.post_date DESC
LIMIT 0, 15
  • Your tax query have relation but only one taxonomy. How? – Jevuska Mar 22 '16 at 21:43
  • Thanks for the suggestion, @Jevuska. You're not supposed to, but you can. The above is merely an example, and in a live environment there will be multiple tax arrays within the tax_query. – Benjamin Mar 22 '16 at 21:52
0

I've discovered a solution. I don't know if it's the best solution, but it does work.

GROUP BY wp_posts.ID was causing my query to error out. I don't quite understand why. In theory, my query should be returning duplicate posts without it... but it's not. So, I'm going to push this code live as it is working as intended.

For concordance, this is the filter and corresponding function which I added to my construct function:

add_filter('posts_groupby', array($this, 'my_posts_groupby'));

function my_posts_groupby($groupby) {
    global $wpdb;
    $groupby = "";
    return $groupby;
}

For more information, read Filter GROUP BY on WP ORG codex.

0

Found solution to your problem... WordPress is putting $groupby after $where, -- in your example it is causing an MySQL error as GROUP BY goes before HAVING in MySQL syntax. solution is to add GROUP BY inside $where statement in posts_where function :-)

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.