sort

Sort On Custom Field Date In ‘mm/dd/yyyy’ Format

There are often posts in the WP Support Forum asking how to sort on a Custom Field which contains a date in ‘mm/dd/yyyy’ format. This is not straight forward because you cannot sort directly on a date in that format.
Consider the following dates:

02/10/2010
08/25/2014
07/01/2015

These dates are in ascending order, but if sorted in the ‘mm/dd/yyyy’ format, the order would be:

02/10/2010
07/01/2015
08/25/2014

This is obviously incorrect.  To sort in the proper order, dates must be in a format with the year first, followed by month and then day, i.e. the most significant field (the one which changes slowest) must be first.

So, the easiest way to get your posts to sort on a date in a Custom Field is to store the value in ‘yyyy/mm/dd’ format.

But, what if you already have a large number of posts with the date in ‘mm/dd/yyy’ format?  The answer then is to use filters on the query to add a sort key with the date rearranged into the proper order.  Here is one way to do that.

First, add these functions to functions.php:

// Filter functions to modify a query.
function mam_posts_fields ($fields) {
   global $mam_global_fields;
   // Make sure there is a leading comma
   if ($mam_global_fields) $fields .= (preg_match('/^(\s+)?,/',$mam_global_fields)) ? $mam_global_fields : ", $mam_global_fields";
   return $fields;
}
function mam_posts_join ($join) {
   global $mam_global_join;
   if ($mam_global_join) $join .= " $mam_global_join";
   return $join;
}
function mam_posts_where ($where) {
   global $mam_global_where;
   if ($mam_global_where) $where .= " $mam_global_where";
   return $where;
}
function mam_posts_orderby ($orderby) {
   global $mam_global_orderby;
   if ($mam_global_orderby) $orderby = $mam_global_orderby;
   return $orderby;
}
add_filter('posts_fields','mam_posts_fields');
add_filter('posts_join','mam_posts_join');
add_filter('posts_where','mam_posts_where');
add_filter('posts_orderby','mam_posts_orderby');

Then use code similar to this to do the query:

// Add a sort key field containing the Custom Field mm/dd/yyyy rearranged to yyyy/mm/dd
$mam_global_fields = ', CONCAT(SUBSTRING(pm.meta_value,7,4), "/", SUBSTRING(pm.meta_value,1,5)) as sort_key';
// Join the postmeta table and set the sort order.
// Be sure to use your own meta_key name in the join!
$mam_global_join = "JOIN $wpdb->postmeta pm ON (pm.post_id = $wpdb->posts.ID AND pm.meta_key = 'eventdate')";
$mam_global_orderby = 'sort_key DESC';

// Query arguments other than 'meta_key', 'orderby', and 'order
// because these will be handled by the filters.
$args = array(
   'posts_per_page' => -1,
   'ignore_sticky_posts' => 1,
   'order' => 'DESC',
);
query_posts($args);

$mam_global_fields = $mam_global_join = $mam_global_orderby = ''; // Clear the filters

if ( have_posts() ) {
   while ( have_posts() ) {
      the_post();
      // Use your own code for the display
      the_title();
      $eventdate = get_post_meta($post->ID, 'eventdate', true);
      echo " Event Date: $eventdate SORT KEY:$post->sort_key<br />";
   }
}

 

Sort Posts on Multiple Custom Fields

This code is in response to a request to sort posts on 6 Custom Fields, but the approach should apply to any number of Custom Fields.

Adding 6 joins to the query would result in significant overhead to the query, so an alternate method was developed which adds no additional joins or queries.

The meta_keys are named in an array. The first key is used to select posts, so all posts must have at least this Custom Field.

Continue reading

Simple Form To Select A Category And Sort Order

There is a lot of code here, and the output doesn’t look very pretty, but it illustrates a basic form to let the Viewer select a Category and sort order for the Twenty Ten theme. Continue reading

List Post Titles Sorted on Custom Fields

The sample code here shows how to sort on the values of two Custom Fields, ‘Business Category’, and ‘Subcategory’, to produce a listing like this:

Automotive

Auto Accessories

  • Acme Auto
  • Bleppo Auto Specialties

Auto Parts

  • Speedy Auto
  • Z’s Auto Parts

The approach is to use a custom query to select the posts and the two custom fields and specify the correct sort order.  The current category and subcategory are used to create control breaks for the proper styling.


<?php

// List posts by Business Category, Subcategory
$sql = "
SELECT p.*,m1.meta_value as category, m2.meta_value as subcategory
FROM $wpdb->posts p
JOIN $wpdb->postmeta m1 ON (p.ID = m1.post_id AND m1.meta_key = 'Business Category')
JOIN $wpdb->postmeta m2 ON (p.ID = m2.post_id AND m2.meta_key = 'Subcategory')
WHERE p.post_type = 'post'
   AND p.post_status = 'publish'
ORDER BY m1.meta_value, m2.meta_value, p.post_title
";
$all_posts = $wpdb->get_results($sql);
$curr_cat = '';
$curr_subcat = '';
$in_list = false;
foreach ($all_posts as $post) {
   setup_postdata($post);
   $this_cat = $post->category;
   $this_subcat = $post->subcategory;
   if ($this_cat != $curr_cat) {
      $curr_cat = $this_cat;
      if ($in_list) {
         echo "</ul>\n";
         $in_list = false;
      }
      echo "<h2>$curr_cat</h2>\n";
      $curr_subcat = '';
   }
   if ($this_subcat != $curr_subcat) {
      $curr_subcat = $this_subcat;
      if ($in_list) {
         echo "</ul>\n";
      }
      echo "<h3>$curr_subcat</h3>\n";
      echo "<ul>\n";
      $in_list = true;
   } ?>
   <li><a href="<?php echo get_permalink($postid); ?>" title="<?php the_title(); ?>"><?php the_title(); ?></a></li>
<?php }
if ($in_list) echo "</ul>\n";

?>