WordPress.org

Ready to get started?Download WordPress

Forums

[resolved] Help with SQL syntax! (2 posts)

  1. nudnik
    Member
    Posted 6 years 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!

  2. nudnik
    Member
    Posted 6 years ago #

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

Topic Closed

This topic has been closed to new replies.

About this Topic