SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Enthusiast
    Join Date
    Mar 2006
    Posts
    81
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    tags table search help

    ok i have 3 tables (simplified for example):

    table posts
    ---------------------------
    postid int()
    title varchar()
    ---------------------------

    tags
    ---------------------------
    tagid int()
    name varchar()
    ---------------------------

    post_tags
    ---------------------------
    tagid int()
    postid int()
    ---------------------------

    Ok i want to be able the user to search using multiple tags to find posts. The system only allows for users to look for tags that are already in the tags table, so no need for % like % or anything. Just strait, search = tags.name.

    There are 5000 posts so far and there are about 1000 tags, each post can have many tags.

    The user can search for as many tags as they like.

    First what is the most efficient way of doing this? I cant help the feeling that finding all the related post/tags then all the places where the tag.name = the search is super inefficient.

    Finally as there can be multiple tags being searched for is there a way to make results that have all tags be at the top, then ones with multiple tags then results with only one tag.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,241
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Code:
    SELECT posts.postid
         , post.title
         , COUNT(*) AS tagged_count
      FROM tags
    INNER
      JOIN post_tags
        ON post_tags.tagid = tags.tagid
    INNER
      JOIN posts
        ON posts.postid = post_tags.post_id
     WHERE tags.name IN ( 'tall','blonde','smart','hot','rich' )
    GROUP
        BY posts.postid
    ORDER
        BY tagged_count DESC
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast
    Join Date
    Mar 2006
    Posts
    81
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks this worked fantastically.

    I've been trying for about an hour now to try and get all the tags that belong to a certain post within that same query. Been looking at recursive queries and sub queries but cant get my head around it.

    Is it even possible????

    Basically i want everything to stay the same, but any results have all there tag.name values as well???

    Thanks again

  4. #4
    SitePoint Enthusiast
    Join Date
    Mar 2006
    Posts
    81
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    is there perhaps a way using sql variables, to have a subquery that gets all the results and then creates a column "tag1,tag2,tag3" or "tag1|tag2|tag3" and then i could do the rest with php???? :S

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,241
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    i can do this with sql, but it would be quite complex, as it would require additional joins

    here's a suggestion

    the above query retrieves the ids of all the posts that you want to display, and the descending count of hits

    use the list of ids in a second query, which retrieves all the data for each post

    separating "search" from "display" logic makes it easier to make future changes

    if i were to give you a single query that does it all, you'd only be back here soon asking to make changes to it
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Enthusiast
    Join Date
    Mar 2006
    Posts
    81
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    haha cool, thanks alot yeah, i have it working with an additional query but thought id try and get it in one (the query is on a page being called from an ajax search so wanted to try and make it as efficient as possible).

    Think your right being left as two, there is a fair bit more i need to display as well (just kept simple on here) so is prob for the best

    Thanks again man, appreciated


Bookmarks

Posting Permissions

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