Order WP_Query results by meta key value in custom query

Question

I have a query used to search projects based on default WordPress fields as well as meta values. I want to sort the results based on the meta key named project_date, which is a timestamp. Here is the SQL I have so far.

SELECT wp_posts.* FROM wp_posts
INNER JOIN wp_postmeta AS mt1 ON (wp_posts.id = mt1.post_id)
WHERE 1 = 1
AND ((wp_posts.post_title LIKE '%testingsearch%') OR (mt1.meta_key = 'description' AND mt1.meta_value LIKE '%testingsearch%')))
AND wp_posts.post_type = 'project'
AND ((wp_posts.post_status = 'publish'))
GROUP BY wp_posts.id

I'm not sure how to include the meta field project_date in an ORDER BY. Obviously something like this won't work since you can't use a WHERE in the ORDER BY segment:

ORDER BY mt1.meta_value WHERE mt1.meta_key = 'project_date' DESC

How can I sort by this meta_key?

Answer 1

As written your question is off-topic, as it is purely SQL-related.

However, the following 2 solutions translate it into WP-ese.

Solution 1

$args = array (
    'post_type' => 'project',
    'post_status' => 'publish',
    's' => 'testingsearch',
    'meta_query' => array (
        'relation' => 'OR',
        'project_date' => array (
            'key' => 'project_date',
            'compare' => 'EXISTS',
            ),
        array (
            'key' => 'description',
            'value' => 'testingsearch',
            'compare' => 'LIKE',
            ),
        ),
    'orderby' => 'project_date',
    'order' => 'DESC',
    ) ;

$p = new WP_Query ($args) ;

This solution takes advantage of the Query improvements in WP 4.2: ‘orderby’ and ‘meta_query’.

However, the query it performs is a little more promiscuous than that in your question. In particular, it searches post_content and post_excerpt in addition to post_title.

If searching those additional fields is OK for your use-case, then you're done. If not, then see Solution 2.

SQL Produced

SELECT SQL_CALC_FOUND_ROWS wp_posts.ID
FROM
    wp_posts INNER JOIN
    wp_postmeta ON ( wp_posts.ID = wp_postmeta.post_id ) INNER JOIN
    wp_postmeta AS mt1 ON ( wp_posts.ID = mt1.post_id )
WHERE 1=1 AND
    ((
        (wp_posts.post_title LIKE '%testingsearch%') OR
        (wp_posts.post_excerpt LIKE '%testingsearch%') OR
        (wp_posts.post_content LIKE '%testingsearch%')
     )) AND
    ( 
        wp_postmeta.meta_key = 'project_date' 
        OR 
        ( mt1.meta_key = 'description' AND mt1.meta_value LIKE '%testingsearch%' )
    ) AND
    wp_posts.post_type = 'project' AND
    ((wp_posts.post_status = 'publish'))
GROUP BY wp_posts.ID
ORDER BY wp_postmeta.meta_value DESC
LIMIT 0, 10

Solution 2

add_filter ('posts_where', 'wpse_posts_where', 10, 2) ;

function
wpse_posts_where ($where, $query)
{
    global $wpdb ;
    if ($search = $query->get ('_title_only')) {
        $search = '%' . $wpdb->esc_like ($search) . '%' ;
        $where .= $wpdb->prepare (" AND {$wpdb->posts}.post_title LIKE '%s'", $search) ;
        }

    return ($where) ;
}

$args = array (
    'post_type' => 'project',
    'post_status' => 'publish',
    // the "private" _title_only arg is "trapped" by the function above that is
    // hooked into 'posts_where' to limit the search to only post_title
    '_title_only' => 'testingsearch',
    'meta_query' => array (
        'relation' => 'OR',
        'project_date' => array (
            'key' => 'project_date',
            'compare' => 'EXISTS',
            ),
        array (
            'key' => 'description',
            'value' => 'testingsearch',
            'compare' => 'LIKE',
            ),
        ),
    'orderby' => 'project_date',
    'order' => 'DESC',
    ) ;

$p = new WP_Query ($args) ;

This solution works just like Solution 1 as far as the ordering by your post_meta, but hooks into posts_where to limit the search to post_title.

Note the use of wpdb::esc_like() and wpdb::prepare() in the func hooked into posts_where to protect against SQL-injection attacks.

SQL Produced

SELECT SQL_CALC_FOUND_ROWS wp_posts.ID
FROM
    wp_posts INNER JOIN
    wp_postmeta ON ( wp_posts.ID = wp_postmeta.post_id ) INNER JOIN
    wp_postmeta AS mt1 ON ( wp_posts.ID = mt1.post_id )
WHERE 1=1 AND
    ( 
        wp_postmeta.meta_key = 'project_date' 
        OR 
        ( mt1.meta_key = 'description' AND mt1.meta_value LIKE '%testingsearch%' )
    ) AND
    wp_posts.post_type = 'project' AND
    ((wp_posts.post_status = 'publish')) AND
    wp_posts.post_title LIKE '%testingsearch%'
GROUP BY wp_posts.ID
ORDER BY wp_postmeta.meta_value DESC
LIMIT 0, 10