1

I'm stumped. I've done a bunch of work to filter what posts get pulled in a WP search and then even more work sorting those after they're pulled from the DB. My main problem now is getting results matched by Tag. I've just been running the straight SQL query and checking the output rather than running it through WP over and over.

First the search query as WP generates it (my filters added and I've expanded out the important part):

SELECT SQL_CALC_FOUND_ROWS  
wp_3_posts.ID FROM wp_3_posts  
LEFT JOIN wp_3_term_relationships AS trel ON (wp_3_posts.ID = trel.object_id)
LEFT JOIN wp_3_term_taxonomy AS ttax ON (  ( ttax.taxonomy = 'post_tag' )  
AND trel.term_taxonomy_id = ttax.term_taxonomy_id) 
LEFT JOIN wp_3_terms AS tter ON (ttax.term_id = tter.term_id)  
WHERE 1=1  
AND 
(
 (
  (
   (
    (wp_3_posts.post_title LIKE '%neck%') 
    OR
    (wp_3_posts.post_content LIKE '%neck%')
   ) 
   AND 
   (
    (wp_3_posts.post_title LIKE '%pain%') 
    OR
    (wp_3_posts.post_content LIKE '%pain%')
   )
  ) 
  OR 
  (
   (
    (tter.name LIKE '%neck%')
    AND
    (tter.name LIKE '%pain%')
   )
   OR
    (tter.name LIKE '%neck pain%')
  )  
  OR 
  (
   (
    (wp_3_posts.post_excerpt LIKE '%neck%') 
    AND
    (wp_3_posts.post_excerpt LIKE '%pain%')
   ) 
   OR
   (wp_3_posts.post_excerpt LIKE '%neck pain%')
  ) 
 )
)  
AND (wp_3_posts.post_password = '')  
AND wp_3_posts.post_type IN ('post', 'page', 'attachment', 'syndicated') 
AND (wp_3_posts.post_status = 'publish')  
ORDER BY wp_3_posts.post_date DESC LIMIT 0, 7

The first part which grabs the necessary tables with joins seems to be just fine. The search I'm running is for "neck pain" and any posts with those words in the title, content, or excerpt show up as expected. I have one post tagged "neck", "pain", and "neck pain". That post ID does not return with this query. What driving me nuts is that if I remove the part of this query that looks into the post_content and post_title, my tagged post does show up. Specifically, if I remove this:

(((wp_3_posts.post_title LIKE '%neck%') 
OR (wp_3_posts.post_content LIKE '%neck%')) 
AND ((wp_3_posts.post_title LIKE '%pain%') 
OR (wp_3_posts.post_content LIKE '%pain%'))) 
OR 

which leaves my query as

SELECT SQL_CALC_FOUND_ROWS  
wp_3_posts.ID FROM wp_3_posts  
LEFT JOIN wp_3_term_relationships AS trel ON (wp_3_posts.ID = trel.object_id)
LEFT JOIN wp_3_term_taxonomy AS ttax ON (  ( ttax.taxonomy = 'post_tag' )  
AND trel.term_taxonomy_id = ttax.term_taxonomy_id) 
LEFT JOIN wp_3_terms AS tter ON (ttax.term_id = tter.term_id)  
WHERE 1=1  
AND 
(
 ( 
  (
   (
    (tter.name LIKE '%neck%')
    AND
    (tter.name LIKE '%pain%')
   )
   OR
    (tter.name LIKE '%neck pain%')
  )  
  OR 
  (
   (
    (wp_3_posts.post_excerpt LIKE '%neck%') 
    AND
    (wp_3_posts.post_excerpt LIKE '%pain%')
   ) 
   OR
   (wp_3_posts.post_excerpt LIKE '%neck pain%')
  ) 
 )
)  
AND (wp_3_posts.post_password = '')  
AND wp_3_posts.post_type IN ('post', 'page', 'attachment', 'syndicated') 
AND (wp_3_posts.post_status = 'publish')  
ORDER BY wp_3_posts.post_date DESC LIMIT 0, 7

Maybe there's something I'm not understanding about SQL here, I'm a bit of a hack at it. Any wisdom would be much appreciated, thanks!

  • Suggestion: Format the queries in real code blocks by removing the ticks and replacing them by 4 whitespaces indentation. Thereafter break it up into newlines at the appropriate places (SQL clauses and operators). Make it readable. You'll be much more likely to receive adequate help. – Johannes Pille Sep 25 '13 at 5:39
  • So I used your initial query on a new blog with a post titled "Testing12" and tags "neck" "pain" and "neck pain" and I got the ID back for the post. It seems to be working for me so far. I will add more posts with variations and see what I get. – dhechler Sep 25 '13 at 17:32
0

The answer is... I'm an idiot! (sort of)

My query is fine. It's not pulling the post I expected it to because I had a LIMIT of 7 and when organized by date my expected post was further than 7 down the line. I was getting confused because the end result of this is a search page with posts organized with matching tags counting for more relevance than matching words in post content, but that organization only happens after the SQL query.

Special thanks @dechler for jogging my brainand making me realize that his attempt would work because he had less posts to be searching through.

| improve this answer | |

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.