Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 4 of 4
  1. #1
    Regular Coder
    Join Date
    Mar 2009
    Posts
    116
    Thanks
    29
    Thanked 1 Time in 1 Post

    Search query for posts with multiple tags

    Hi there,
    Well i have this schema:


    POSTS
    --post_id
    --Title (fulltext key)


    TAGS
    --tag_id
    --Name


    TAGS-POSTS
    --tag_id
    --post_id

    So my main goal is make a full-text search but taking on the post's title but also considering it's tags if possible. If it is not possible another solution could be having the post's tags inside the POSTS table and index them as full-text keys.

    Any ideas are appreciated, thanks in advance
    Last edited by hernantz; 07-31-2011 at 08:12 PM.

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,171
    Thanks
    80
    Thanked 4,560 Times in 4,524 Posts
    Not sure I see why you would need/want a FULL-TEXT search for this purpose.

    Since you have a fixed set of tags, each with unique name (and id...and that's good db design!), you don't need to use full-text. You just need to search for matches.

    And, for that matter, if you convert the tags-to-search-for into id's, first, then you just have to look for id's.

    But sticking with the tag names, for example, you could do something like:
    Code:
    SELECT DISTINCT posts.* 
    FROM posts, tags_posts, tags
    WHERE posts.post_id = tags_posts.post_id
    AND tags_posts.tag_id = tags.tag_id
    AND tags.name IN ('elephant', 'zebra', 'hydra')
    (Okay, silly tags.name values, but you get the idea. It's a simple JOIN, and a simple list of names passed to an IN clause. Very efficient. More so than full-text would be.)

  • Users who have thanked Old Pedant for this post:

    hernantz (08-02-2011)

  • #3
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,171
    Thanks
    80
    Thanked 4,560 Times in 4,524 Posts
    Naturally, this has nothing to do with whether or not you do full-text search on the post title or contents or whatever.

  • #4
    Regular Coder
    Join Date
    Mar 2009
    Posts
    116
    Thanks
    29
    Thanked 1 Time in 1 Post
    Quote Originally Posted by Old Pedant View Post
    Not sure I see why you would need/want a FULL-TEXT search for this purpose.

    Since you have a fixed set of tags, each with unique name (and id...and that's good db design!), you don't need to use full-text. You just need to search for matches.

    And, for that matter, if you convert the tags-to-search-for into id's, first, then you just have to look for id's.

    But sticking with the tag names, for example, you could do something like:
    Code:
    SELECT DISTINCT posts.* 
    FROM posts, tags_posts, tags
    WHERE posts.post_id = tags_posts.post_id
    AND tags_posts.tag_id = tags.tag_id
    AND tags.name IN ('elephant', 'zebra', 'hydra')
    (Okay, silly tags.name values, but you get the idea. It's a simple JOIN, and a simple list of names passed to an IN clause. Very efficient. More so than full-text would be.)
    Hi old-pedant
    Thanks for your answer. Well that join is supposed to give results that have those tags right?
    But maybe i did not explain myself well, and what i needed is something like this http://stackoverflow.com/questions/1...arch-with-tags

    But i decided i will do some dirty trick and just insert and repeat all the normalized tags into the posts table. So a full-text index on them will get the most relevant results, although thats not a good db design it will let the hard work to the full-text search engine.....

    Just as stackoverflow did for searching related questions.....


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •