0

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?

1 Answer 1

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
3
  • Thank you very much for still taking the time to answer the question. I was unsure where to post the question, because I was pretty sure if I posted on StackOverflow they would say it's WordPress related and delete it. Anyway, thanks again. I tried using the "WP-ese" syntax previously and just couldn't get it to create the query I wanted. I think with your help above I may be able to get it.
    – Gavin
    Apr 16, 2017 at 16:37
  • I just edited my answer...to correct a typo (I originally used 'post_type' => 'post' instead of 'post_type' => 'project') and to add the SQL that WP_Query produces for both solutions (to help you understand how the solutions work). Apr 16, 2017 at 16:56
  • I'm actually not sure if this is doing what I need. For the search, it needs to find "testingsearch" in the post_title OR the description meta value, but this seems to be requiring the search term is always in the post_title and optionally in the meta value, right?
    – Gavin
    Apr 17, 2017 at 11:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.