Filtering on a Non-Standard Database Field with WordPress

There’s one primary distinction between Drupal and WordPress. In Drupal, URLs map to PHP functions. In WordPress, URLs map to documents (or, quite often, collections of documents). The former can certainly be powerful and easy to work with. The latter, once you get used to the paradigm, can provide an incredible simplicity, powerful in its own way.

I was building a website today that has a location-based search component. Basically, I have a collection of posts with addresses, and I need to be able to search for addresses within a given distance of a queried address, and sort them by said distance. To be honest, that’s hard to do with WordPress. But it is possible…

Step 1: Create a Table to Hold Coordinates

To start out, you need to add a database table to hold the coordinates for each address. So use register_activation_hook() to point to a function that adds your table:

  function my_activation_function() {
    global $wpdb;
    require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
    foreach ( array('providers') as $table ) {
      $sql = "CREATE TABLE ".$wpdb->prefix."coordinates (
                pid bigint(20) unsigned NOT NULL,
                latitude double,
                longitude double,
                UNIQUE KEY pid (pid),
                INDEX latitude (latitude),
                INDEX longitude (longitude)
              ) /*!40100 DEFAULT CHARACTER SET utf8 */;";
      dbDelta($sql);
    }
  }

I’m using the Google Geocoding API to populate this table based on an address I enter when creating a post.

Step 2: Look for a Query Parameter

I’m going to look for a query parameter that I’ll designate as ll (short for “latitude/longitude”), so you can do a search with a URL like http://example.com/?ll=40.0,-85.7. We need to tell WordPress to look for this parameter, using the query_vars filter.

  function my_query_vars_filter( $array ) {
    $array[] = 'll';
    return $array;
  }

Pretty simple, no? That just tells WordPress to add ll to the WP_Query->query_vars array if it’s present in the URL.

Step 3: Modify the SQL Query

Now for the hard part: we need to tell WordPress to modify the SQL query it uses to select posts if this parameter is present. To do this, we’ll use a collection of filters: posts_fields, posts_join, posts_groupby, and posts_orderby.

First, we’ll use posts_fields to add a field to the SELECT statement. By default, the query returns wp_posts.*. We need to add a field for the distance from the given coordinates to each post’s coordinates. I’ll call that field distance.

  function posts_fields( $fields, $query ) {
    global $wpdb;
    if ( $query->query_vars['ll'] ) {
      list($lat, $lng) = explode(',', $query->query_vars['ll']);
      $fields .= sprintf(", ( 3959 * acos( cos( radians( %f ) ) * cos( radians( ".$wpdb->prefix."coordinates.latitude ) ) * cos( radians( ".$wpdb->prefix."coordinates.longitude ) - radians( %f ) ) + sin( radians( %f ) ) * sin( radians( ".$wpdb->prefix."coordinates.latitude ) ) ) ) AS distance", $lat, $lng, $lat);
    }
    return $fields;
  }

Without getting into the details of the math, this tells MySQL to calculate the distance between two sets of coordinates in miles (3,959 is the radius of the earth in miles) and call the result distance.

posts_join adds a JOIN clause to the query, so we can link the posts table with our coordinates table.

  function posts_join( $join, $query ) {
    global $wpdb;
    if ( $query->query_vars['ll'] ) {
      $join .= " LEFT JOIN ".$wpdb->prefix."coordinates ON ".$wpdb->posts.".ID = ".$wpdb->prefix."coordinates.pid";
    }
    return $join;
  }

posts_groupby is used to limit to posts within a certain distance. It’s not strictly necessary, and you could conceivably add another query parameter to make this configurable.

  function posts_groupby( $groupby, $query ) {
    global $wpdb;
    if ( $query->query_vars['ll'] ) {
      $groupby .= $wpdb->posts.".ID HAVING distance < 100";
    }
    return $groupby;
  }

Finally, posts_orderby orders the results by distance (instead of the default post date).

  function posts_orderby( $orderby, $query ) {
    global $wpdb;
    if ( $query->query_vars['ll'] && $query->query_vars['post_type'] == 'provider' ) {
      $myorder = "distance ASC";
      if ( $orderby ) {
        $myorder .= ", ".$orderby;
      }
    }
    return $myorder?$myorder:$orderby; // return the default if we haven't changed it
  }

Notice that we need to prepend the value here, to override the default sort.

And there you have it. Quite a lot to go through, but the end result works exactly as intended.