1

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.

3
  • 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
    – Ismail
    May 17, 2016 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. May 17, 2016 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.
    – Ismail
    May 17, 2016 at 15:21

2 Answers 2

2

Is this what you're trying to do?

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

// The Query
$query = new WP_Query( $args );
1

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

1
  • After changing the table prefix and the changing .meta_key to .meta_value this wouldn't work for me but thanks May 17, 2016 at 18:15

Your Answer

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

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