Help with SQL syntax!

  • Resolved nudnik

    (@nudnik)


    16 years, 10 months ago

    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!

Viewing 1 replies (of 1 total)
  • Thread Starter nudnik

    (@nudnik)

    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):
    https://wordpress.pastebin.com/f552e07ae

Viewing 1 replies (of 1 total)
  • The topic ‘Help with SQL syntax!’ is closed to new replies.