sql explained for group tagroom


"SELECT b.* FROM posts b, taggings c
            WHERE `delete` = ''  AND ( b.status = 'publish'  OR (b.status = 'private' AND  b.group = '%s' ) )
            AND b.`group` = '%s'
            AND  c.id = b.id
            AND (c.tag IN (%s))
            GROUP BY b.id
            HAVING COUNT( b.id )= %s
            ORDER BY date DESC LIMIT %s , %s ",
            $cookieGroup,
            $group,
            $list,
            $count,
            $offset,
            $postsPerPage);

One wonders what this clause means:
          
    AND (c.tag IN (%s))

Here %s is just the comma delimited string of the tags in the tag hierarchy.  So the tag must exist in the list of tags that we are anding or we are certainly not interested in it. 

One wonders what this clause means:

            GROUP BY b.id
            HAVING COUNT( b.id )= %s

For %s we substitute the number of tags in the hierarchy.  If we are in s $bug,rte, then the count = 2.  If we are in s $bug,rte,ads then the count = 3.  So interprete the clause as meaning each item (b.id) must be tagged with each of the tags in the heirachy.  Since we cannot have duplicate tags on the same item, (that is a bug we fixed),  this effectively does the anding necessary for intersection  of sets ... from wikepedia intersection (set theory):


Where A might be $bug and B might be rte Then the heirachy $bug,rte contains items that are tagged with "$bug" and also tagged with "rte".  Note that with tagging (and folksonomy in general) we can get a hierarchy like s $bug,rte,ads but the order of the hierarchy is absolutely arbitrary. 





Tags

  1. programming
  2. _tags
  3. folksonomy
  4. group tagroom
  5. sql

Comments


Mark de LA says
seth 2006-02-08 16:32:40 1813
Here is a better diagram:
In a universe of assholes I guess this would work pretty well!




Seth says
Here is a better diagram:




See Also

  1. Thought Do we need system tags? with 19 viewings related by tag "_tags".
  2. Thought Footer problem on individual page at speaktomecatalog.com with 19 viewings related by tag "programming".
  3. Thought about: Gather | Welcome with 6 viewings related by tag "folksonomy".
  4. Thought Linking fastblogit tags to other sites with 4 viewings related by tag "folksonomy".
  5. Thought Quads with 3 viewings related by tag "folksonomy".
  6. Thought automatic bookmarks and intentional bookmarks with 3 viewings related by tag "folksonomy".
  7. Thought about: periscope data with 3 viewings related by tag "sql".
  8. Thought Global Heretics with 3 viewings related by tag "folksonomy".
  9. Thought Promotions in the Speak To Me Catalog Shopping Basket with 2 viewings related by tag "programming".
  10. Thought test of tags with blank in front of word with 2 viewings related by tag "_tags".
  11. Thought What are related tags? with 1 viewings related by tag "folksonomy".
  12. Thought about: Micro Persuasion: The Missing Piece in the RSS Puzzle with 1 viewings related by tag "folksonomy".
  13. Thought about: Then each went to his own home with 1 viewings related by tag "folksonomy".
  14. Thought about: MetaWishlist.com: The social wish list repository with 1 viewings related by tag "folksonomy".
  15. Thought about: We Dont Need No Stinking Domain Names with 1 viewings related by tag "folksonomy".
  16. Thought related obscene tags with 1 viewings related by tag "_tags".
  17. Thought control for ~ (censor tags not converted) with 1 viewings related by tag "_tags".
  18. Thought funny page cartoon with 1 viewings related by tag "folksonomy".
  19. Thought about: try ruby! (in your browser) with 1 viewings related by tag "programming".
  20. Thought How to improve your tagging ? with 1 viewings related by tag "folksonomy".
  21. Thought about: Hello World: Tag-cloud my OPML with 1 viewings related by tag "folksonomy".
  22. Thought unzip for install.php with 1 viewings related by tag "programming".
  23. Thought How does bookmarking your memes at del.icio.us or flickr spread them? with 1 viewings related by tag "folksonomy".
  24. Thought SQL for selecting triples inside of one field. with 1 viewings related by tag "programming".
  25. Thought some thoughts on trans domain data transfer with 1 viewings related by tag "programming".
  26. Thought Please fix bad tags with 1 viewings related by tag "_tags".
  27. Thought a good place to satisfy question~answer with 0 viewings related by tag "programming".
  28. Thought about: Google Image Labeler with 0 viewings related by tag "folksonomy".
  29. Thought about: Burningbird with 0 viewings related by tag "folksonomy".
  30. Thought automatically update all taggings at del.icio.us with 0 viewings related by tag "_tags".
  31. Thought about: P. S. : ? Tagclouds and cultural changes with 0 viewings related by tag "folksonomy".
  32. Thought sources of php code with 0 viewings related by tag "programming".
  33. Thought about: Eurekster Swicki Home with 0 viewings related by tag "folksonomy".
  34. Thought What is a tag ? with 0 viewings related by tag "folksonomy".
  35. Thought about: Wink with 0 viewings related by tag "folksonomy".
  36. Thought Tagging Is A Better Memory Substitute Than Hyperlinking with 0 viewings related by tag "folksonomy".
  37. Thought Why do we tag? with 0 viewings related by tag "folksonomy".
  38. Thought related tags with 0 viewings related by tag "programming".
  39. Thought Something New ? with 0 viewings related by tag "folksonomy".
  40. Thought deactivate HTML in comments - or how and where to escape with 0 viewings related by tag "programming".
  41. Thought propogate logic from front door to rooms and refactor with 0 viewings related by tag "programming".
  42. Thought Related tags don't works in groups ? with 0 viewings related by tag "_tags".
  43. Thought ok here is the sizing algorithm that will go into testing with 0 viewings related by tag "programming".
  44. Thought cleaning up tags with 0 viewings related by tag "_tags".
  45. Thought about: Technorati: Mini with 0 viewings related by tag "_tags".
  46. Thought about: persuing inter domain tagging with 0 viewings related by tag "folksonomy".
  47. Thought against hierarchy with 0 viewings related by tag "folksonomy".
  48. Thought the multitudes with 0 viewings related by tag "folksonomy".
  49. Thought a method of providing for private tags with 0 viewings related by tag "_tags".
  50. Thought automatic tags for tagroom with 0 viewings related by tag "_tags".