Using Named Queries To Order By Custom Fields In Wordpress

Getting Data from Custom Fields in Order

When building a WordPress site, you may want to retrieve posts ordered by custom field values rather than the default parameters like date or title. However, the common WordPress functions like get_posts() and WP_Query() do not provide direct ways to sort by custom fields.

In this article, we will explore how to use named queries – a lesser known feature of WordPress – to order results from the postmeta table by custom field values. We will cover the basics of defining named queries, the orderby parameter for sorting by custom fields, joining the postmeta table, and displaying the ordered data on the front end.

The Problem: Retrieving Custom Field Data in a Specific Order

WordPress stores post content (title, content, excerpt etc.) in the wp_posts table of the database. Additional information like custom fields and metadata is stored separately in the wp_postmeta table.

Table of Contents

When you query WordPress for posts using functions like get_posts() or WP_Query(), it returns results ordered by default parameters like the date, title, author etc. There is no direct way to order them by values of custom fields.

For example, if you wanted to display posts ordered by a custom ‘event_date’ field, it would require querying data from both wp_posts and wp_postmeta tables and ordering the combined results by the meta value.

Understanding get_posts() and WP_Query()

The most common way of querying posts in WordPress is using:

get_posts() function
WP_Query class

get_posts() is a simple helper function which creates a WP_Query instance with provided arguments. WP_Query does the heavy lifting of querying posts from the database.

Both get_posts() and WP_Query() take a set of query arguments like post_type, posts_per_page etc. that filters the posts to be returned. Some sorting related parameters are:

orderby – Order posts by date, title, menu order etc.
order – ASC or DESC order

However, there is no direct parameter to order results by custom field values. The common workaround is to pass custom field parameters to filter the query results first, and then do the sorting manually in PHP by iterating over the results.

This can become cumbersome if you need to retrieve and order hundreds or thousands of posts by custom fields.

Introducing Named Queries for Custom Sorting

Named queries are a way to define custom SQL queries in WordPress and assign a name to them. They provide an alternative interface to fetch data from the database tables.

The key advantages of using named queries are:

Join multiple database tables like posts and postmeta.
Use SQL statements like ORDER BY on custom fields.
Save and reuse complex queries with a name.

So for cases where we need fine grained control over sorting query results by custom fields, named queries are very useful.

Defining a Named Query

Named queries need to be defined by using the posts_clauses filter before they can be used in a WP_Query:

add_filter('posts_clauses', 'my_filter_posts_clauses', 10, 2);
function my_filter_posts_clauses($pieces, $query) {

    // Only modify queries for 'event_orderby'
    if ($query->get('event_orderby')) {

        // Define inner join with postmeta
        $pieces['join'] .= "INNER JOIN {$wpdb->postmeta} ON {$wpdb->posts}.ID = {$wpdb->postmeta}.post_id ";
        
        // Custom field to order by 
        $pieces['where'] = "WHERE 1=1 AND ( {$wpdb->postmeta}.meta_key = 'event_date' )";

        // Order by postmeta value  
        $pieces['orderby'] = "{$wpdb->postmeta}.meta_value DESC";
    }
    
    return $pieces;
}

Breaking it down:

Check if its a query for our named query using a custom parameter.
Modify and add JOIN clause to include postmeta table.
Update WHERE condition to filter by custom field key.
Order results by custom field value stored in postmeta.

This defined named query can now be executed using WP_Query:

$query = new WP_Query( ['event_orderby' => true] );

Orderby Parameters for Custom Fields

For ordering by custom fields in named queries, the orderby clause needs to reference the postmeta table and meta_value column.

Some possible options are:

// Order by meta_value of custom field
$pieces['orderby'] = "{$wpdb->postmeta}.meta_value";  

// Explicitly specify ASC or DESC
$pieces['orderby'] = "{$wpdb->postmeta}.meta_value DESC";

// Order by multiple custom fields  
$pieces['orderby'] = "{$wpdb->postmeta}.meta_value DESC, {$wpdb->postmeta}.meta_value ASC";

Additionally, you can order by result of SQL functions on meta_value:

// Order by LENGTH() of meta_value 
$pieces['orderby'] = "LENGTH({$wpdb->postmeta}.meta_value) DESC"

// Order by result of DATE() on date custom field
$pieces['orderby'] = "DATE({$wpdb->postmeta}.meta_value) DESC"

Example Named Query for Date Field

Lets take an example of defining a named query to retrieve and order posts by a custom ‘event_date’ field storing a date in ‘Y-m-d’ format.

// 'event_orderby' named query
add_filter('posts_clauses', 'order_by_event_date');
function order_by_event_date($pieces)  {

    // Only for 'event_orderby' queries
    if ($query->get('event_orderby')) {
    
        // Join postmeta table
        $pieces['join'] .= "INNER JOIN {$wpdb->postmeta} ON {$wpdb->posts}.ID = {$wpdb->postmeta}.post_id ";
        
        // Filter by event_date custom field 
        $pieces['where'] = "WHERE 1=1 AND ( {$wpdb->postmeta}.meta_key = 'event_date' )";

        // Convert date string to SQL date 
        $pieces['orderby'] = "DATE({$wpdb->postmeta}.meta_value) DESC";

        // Specify desired order  
        $pieces['order'] = "ASC"; 
    }

    return $pieces;
}

To execute it:

$query = new WP_Query( ['event_orderby' => true] );

This will return posts ordered chronologically by the ‘event_date’ custom field value.

Using Metadata to Sort Alphabetically

For ordering posts alphabetically by text based custom field values, the POST_EXCERPT metadata can be used instead of actual meta value.

The POST_EXCERPT is a special metadata generated by WordPress to store the first few words or reference of any text content.

For example:

$pieces['orderby'] = "{$wpdb->postmeta}.post_excerpt ASC";

This will order posts by the excerpt (first few words) extracted from custom field values. The excerpt is generated automatically on the first query request.

Joining Post Meta Data Tables

When defining named queries to order by meta values, the postmeta table containing the custom field data needs to be joined with the posts table.

There are some typical ways of joining the tables:

// Inner join posts with postmeta
$pieces['join'] .= "INNER JOIN {$wpdb->postmeta} ON {$wpdb->posts}.ID = {$wpdb->postmeta}.post_id ";

// Left outer join 
$pieces['join'] .= "LEFT OUTER JOIN {$wpdb->postmeta} ON {$wpdb->posts}.ID = {$wpdb->postmeta}.post_id ";

// Join multiple meta keys
$pieces['join'] .=  "INNER JOIN {$wpdb->postmeta} AS mt1 ON {$wpdb->posts}.ID = mt1.post_id "; 
$pieces['join'] .=  "INNER JOIN {$wpdb->postmeta} AS mt2 ON {$wpdb->posts}.ID = mt2.post_id ";

// Self join the postmeta table  
$pieces['join'] .= "INNER JOIN {$wpdb->postmeta} AS pm1 ON pm1.post_id = {$wpdb->postmeta}.post_id";

The best join method depends on the structure of data and custom fields.

Putting It All Together: Full Named Query Example

Lets take an example of a Movie Reviews site displaying lists of movies sorted by ratings, release dates etc.

We want to define a reusable named query to retrieve and order movies by the the following custom fields:

reviewer_rating – Star rating by reviewer
user_rating – Average user rating
release_date – Movie release date

The named query with all the components will be:

add_filter('posts_clauses', 'order_by_movie_fields');
function order_by_movie_fields($pieces) {

    if ($query->get('movie_orderby')) {

        $pieces['join'] = "INNER JOIN {$wpdb->postmeta} AS meta_rev ON {$wpdb->posts}.ID = meta_rev.post_id";
        $pieces['join'] .= "INNER JOIN {$wpdb->postmeta} AS meta_usr ON {$wpdb->posts}.ID = meta_usr.post_id ";
        $pieces['join'] .= "INNER JOIN {$wpdb->postmeta} AS meta_rel ON {$wpdb->posts}.ID = meta_rel.post_id ";

        $pieces['where'] = "WHERE 1=1 AND (
            meta_rev.meta_key = 'reviewer_rating' AND
            meta_usr.meta_key = 'user_rating' AND 
            meta_rel.meta_key = 'release_date'
        )";

        $pieces['orderby'] = "CAST(meta_usr.meta_value AS SIGNED) DESC, DATE(meta_rel.meta_value) DESC"; 
        $pieces['order'] = "ASC";
    }

    return $pieces;
}

Breaking it down:

Inner join each custom field’s postmeta table
Filter to only required custom field keys in WHERE
Order by user_rating numerical value and then release_date

To query movies ordered by these fields:

$query = new WP_Query( ['movie_orderby' => true] );

Displaying Ordered Custom Field Data

When looping over the WP_Query results of a named query, additional steps need to be taken to access the custom field data compared to a regular query.

Some ways of displaying custom field values are:

// Approach 1: Get all post meta in one go
$meta = get_post_meta( get_the_ID() );  

// Access specific custom fields
echo $meta['reviewer_rating'][0]; 

// Approach 2: Individual custom field queries
$user_rating = get_post_meta( get_the_ID(), 'user_rating', true );
echo $user_rating;

The reason is that by default WordPress does not extract custom field values when posts are queried using named queries.

So the meta values need to be explicitly obtained using get_post_meta() inside the loop.

Additional Tips for Custom Sorting

Some additional tips when ordering custom queries:

Use ‘posts_orderby’ filter instead if modifying admin queries like edit posts screen.
Check orderby value before overriding query to prevent conflicts.
Test queries with SQL_CALC_FOUND_ROWS to check performance.
Use caching plugins to save ordered query results.
Use separate named queries per field to allow combining multiple orders.

Using Named Queries To Order By Custom Fields In WordPress