I am trying to search through post meta for keyword matches but I'm new to MySQL and I don't know how to write my query. I can write a basic query for post_title matches but I don't know how to properly jump across tables.

In pseudo code, my query would be

"SELECT post_id FROM $wpdb->postmeta 
WHERE meta_key LIKE '%$query%' AND post_type = 'project'"

My problem is that the post_type is only available in the posts table and I don't know how to make the connection between postmeta and posts to check if the ID is in fact the correct post type.

  • Once you get a list of posts, then filter out the non-wanted post types by unsetting them from the list with "project" !== get_post_type($post_ID) condition.. Just a thought. Or a custom WP_Query would help pastebin.com/jjEEgwsw – Samuel Elh May 17 '16 at 14:49
  • I need my query to be as efficient as possible since I have hundreds of thousands of records to search through. I can already see a noticeable difference between the speed of using $wpdb and WP_Query when searching for post titles. – somebodysomewhere May 17 '16 at 15:06
  • Caching is the key. WP_Query caches requests so you wouldn't worry about the performance and resource usage. Queries made through $wpdb methods won't be cached. – Samuel Elh May 17 '16 at 15:21

Is this what you're trying to do?

// WP_Query arguments
$args = array (
    'post_type'              => array( 'project' ),
    'meta_query'             => array(
            'key'       => 'meta_key',
            'value'     => '%$query%',

// The Query
$query = new WP_Query( $args );
| improve this answer | |

I'm not an expert but try this one:

SELECT wp_posts.ID, wp_postmeta.meta_value FROM wp_postmeta
LEFT JOIN wp_posts ON wp_posts.ID = wp_postmeta.post_id
WHERE wp_postmeta.meta_key = '%$query%'
AND wp_posts.post_type = 'project'

it worked for me

| improve this answer | |
  • After changing the table prefix and the changing .meta_key to .meta_value this wouldn't work for me but thanks – somebodysomewhere May 17 '16 at 18:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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