Extending WP Query: Custom geolocation meta values work, but tax_query breaks

Question

Extending WP Query: Custom geolocation meta values work, but tax_query breaks

Benjamin

2022年3月30日 23:06

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

Topic tax-query meta-query wp-query geo-data Wordpress

Category Web

Arek Mateusiak · Accepted Answer · 2017年3月4日 01:18

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 :-)

Benjamin · Accepted Answer · 2016年3月23日 01:30

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.

Extending WP Query: Custom geolocation meta values work, but tax_query breaks

About