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.

8 Responses to Filtering on a Non-Standard Database Field with WordPress

  1. Hello,

    Can you tell me how to code Classipress (the classified ad theme for WordPress), so that I can load many ads at once through a CSV file that is in my FTP Client and is updated daily? Classipress has horrible support and I can’t find the answer to this question anywhere. However, many people are asking the same question. I am a novice, so please speak slowly.

    I’ll explain the purpose. I want to place ads for several car dealers’ entire inventories in my Classipress Site (onestopadsource.com). Since these have 100s of cars to place into ads: and they are updated daily, I can not possibly keep up with them manually, or individually. Classipress support says they “do not think” it can be done without some coding.

    Thanks

  2. pols says:

    Hello,

    Thanks for this articles, you are my savior.. :).. I spend the whole day searching for posts_fields in the net until I find your site.. this is the only site that has an example for posts_fields.. Thanks

    -pols

  3. Richard says:

    Can you please also show the code you used to hook into the filters?

    • All the filters are listed above. Just use add_filter() in your plugin’s initialization function once for each of the above mentioned filters. E.g.:

      add_filter('posts_fields', array($my_plugin_object, 'posts_fields'), 10, 2);

  4. Julian Hale says:

    Awesome, thanks! This is exactly the project I’m working on right now, except the theme I’m using stores the latitude and longitude as post meta. So I need to figure out if wp_postmeta.metakey = geo_latitude , and then if it does use that value for the calculation… do you know a good way to do that? Do I need to use nested select statements?

    • Yes, you can do it with post meta, too. The query won’t be quite as efficient as if you had done it with a separate table, but depending on the size of your DB, that may not be a big issue. Your join statement would look something like:

            " LEFT JOIN {$wpdb->postmeta} geo ON {$wpdb->posts}.ID = geo.post_id AND geo.meta_key='geo_latitude'";
      

      Then the field geo.meta_value will have your latitude.

    • Julian Hale says:

      Ah, thanks! That got me really close, I just had to figure out how to disambiguate two separate joins to the same table. Now I’m that much more familiar with MySQL.

  5. Lorenzo Gasperoni says:

    Great, this is the ONLY site that gives some documentation to ‘posts_fields’. THANKS!