Order WP_Query results by meta key value in custom query

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