[Resolved] Help with SQL syntax!
Hi, I’m making some custom modifications to the incredible Search-Everything plugin.
I can’t figure out what’s wrong with this query that it doesn’t work:
SELECT DISTINCT SQL_CALC_FOUND_ROWS db_posts.* FROM db_posts LEFT JOIN db_term_relationships AS trel ON (db_posts.ID = trel.object_id) LEFT JOIN db_term_taxonomy AS ttax ON (ttax.taxonomy = 'post_tag' AND trel.term_taxonomy_id = ttax.term_taxonomy_id) LEFT JOIN db_terms AS tter ON (ttax.term_id = tter.term_id) WHERE 1=1 AND (((post_title LIKE '%vacation%') OR (post_content LIKE '%vacation%')) AND ((post_title LIKE '%hawaii%') OR (post_content LIKE '%hawaii%')) OR (post_title LIKE '%vacation hawaii%') OR (post_content LIKE '%vacation hawaii%')) AND post_type = 'post' AND (post_status = 'publish' OR post_status = 'private') OR ( ( tter.slug LIKE '%vacation%') AND ( tter.slug LIKE '%hawaii%') ) OR ( tter.slug LIKE '%vacation-hawaii%') ORDER BY post_date DESC LIMIT 0, 12
I’m thinking that the problem is with “( tter.slug LIKE ‘%vacation%’) AND ( tter.slug LIKE ‘%hawaii%’)” but I don’t know why it shouldn’t work… unless it’s checking both against only one slug at a time?
What I need in English: 🙂 I have a post, let’s say called “My Trip to Maui.” It contains just images, perhaps a poem. It would be tagged “vacation” and “Hawaiian.”
With the SE defaults, searching for “Hawaii vacation” would return ALL posts with either “hawaiian” or “vacation.” Searching with “?tag=” will not return a post with a tag “Hawaiian” because it only uses exact matches.
Any leads are appreciated! Thanks!
OK, after breaking my head on it for hours on end, I got the search functionality I wanted.
First I search for matching tag slugs, and then use the following syntax:
SELECT ID FROM db_posts LEFT JOIN db_term_relationships ON ID = db_term_relationships.object_id WHERE ((term_taxonomy_id = 233) OR (term_taxonomy_id = 140) OR (post_title LIKE '%hawaii%')) AND ID IN (SELECT ID FROM db_posts LEFT JOIN db_term_relationships ON ID = db_term_relationships.object_id WHERE ((term_taxonomy_id = 48) OR (term_taxonomy_id = 233) OR (post_title LIKE '%vacation%')))
Note that I don’t need it to search post content, so it doesn’t! It does, however, match both single and multi-word tags (searching for “new york times” will match the “new-york-times” slug, as well as “new-york” and “good-times”.
In case it will help anyone else, here is the complicated PHP markup (I think it breaks standard functionality of the plugin FYI):
- The topic ‘[Resolved] Help with SQL syntax!’ is closed to new replies.