1

Take this basic query:

$posts = new WP_Query(array(
    'post_type' => 'page'
    's' => 'some search query'
));

WordPress will split the some search query into 3 separate search terms, which is why I need to make this $wpdb function. I want the same functionality, but with the entire query treated as 1 search term.

Here is where I'm at:

global $wpdb;
$post_type = 'product';

$db_matches = $wpdb->get_results("
                                    SELECT ID
                                    FROM $wpdb->posts
                                    INNER JOIN $wpdb->term_relationships ON $wpdb->term_relationships.object_id = $wpdb->posts.post_title
                                    WHERE post_title LIKE '%$query%'
                                        AND post_type = '$post_type'
                                        AND post_status = 'publish'
                                    ");

This is my first inner join ever, so it's probably all wrong. Two things need to be done to fix this query:

  1. Fix the syntax in the inner join line
  2. Add the language filter in the WHERE clauses (should be something like AND term_taxonomy_id = 2, right?). I'm using Polylang, so I just need to check that the object_id exists in the term_relationships table with the value 2.

So can anyone give me a hand with this? Thanks a lot.

  • This appears to be an XY problem, can you explain what you were trying to achieve that required this SQL? It would make more sense to ask about what you're trying to do, than asking about fixing your solution, e.g. it looks like you're unaware that WP_Query can check taxonomies too – Tom J Nowell Oct 31 '17 at 21:37
2
  1. It seems that you in your INNER JOIN, you are joining on

    wp_term_relationships.object_id == wp_posts.post_title
    

    which is erroneous, since object_id is the post-id in this case-scenario.

    I want all IDs (post-ids)

    SELECT ID FROM $wpdb->posts
    

    Who have a record in the wp_term_relationships table

    // Now we are comparing the correct columns
    INNER JOIN $wpdb->term_relationships ON $wpdb->term_relationships.object_id = $wpdb->posts.ID 
    
  2. Adding the additional where clause

    Where the term_taxonomy_id is equal to 2

    WHERE $wpdb->term_relationships.term_taxonomy_id = 2
    
1

WordPress will split the some search query into 3 separate search terms, which is why I need to make this $wpdb function. I want the same functionality, but with the entire query treated as 1 search term.

There are the sentence and exact input search parameters for WP_Query.

Instead of manually constructing the search query yourself, please try e.g.

$query = new WP_Query( [
    'post_type' => 'page',
    's'         => 'some search query',
    'sentence'  => true,
    'exact'     => true,
] );

The generated SQL query will include:

(wp_posts.post_title LIKE 'some search query') OR 
(wp_posts.post_excerpt LIKE 'some search query') OR 
(wp_posts.post_content LIKE 'some search query')

With exact as false, it will change to:

(wp_posts.post_title LIKE '%some search query%') OR 
(wp_posts.post_excerpt LIKE '%some search query%') OR 
(wp_posts.post_content LIKE '%some search query%')

Hope it helps!

  • Wow. I really wish that sentence and exact were in the docs... – Brian Nov 1 '17 at 15:48
  • The best documentation is the source @Brian – birgire Nov 1 '17 at 15:55

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.