Feature request #1209: Scope refinement while navigating the tag cloud

Comments

1. Greg Hurrell 2009-01-26T15:56:51Z

   Status changed:

   • From: New
   • To: Open

2. Greg Hurrell 2009-01-28T05:53:51Z

   Let's start with a self CROSS JOIN (produces exponential growth of records; ie. if there are 1,000 taggings you'll get 1,000,000 rows back):

   SELECT * FROM taggings AS t1 JOIN taggings AS t2;

   Adding a WHERE clause to reduce the number of returned results:

   SELECT * FROM taggings AS t1 JOIN taggings AS t2 WHERE t1.tag_id = 38;

   To see the groupings a little more clearly:

   SELECT * FROM taggings AS t1 JOIN taggings AS t2 WHERE t1.tag_id = 38 ORDER BY t1.id;

   So just say there were 54 items with tag 38, you'd now get 54 x 1,000 rows in the result (54,000).

   SELECT * FROM taggings AS t1
   JOIN taggings AS t2
   WHERE t1.tag_id = 38 AND t1.taggable_id = t2.taggable_id AND t1.taggable_type = t2.taggable_type;

   This yields a big reduction in the returned result size.

   SELECT * FROM taggings AS t1
   JOIN taggings AS t2
   WHERE t1.tag_id = 38 AND t1.taggable_id = t2.taggable_id AND t1.taggable_type = t2.taggable_type
   GROUP BY t2.tag_id;

   And another reduction in result size.

   Now let's just get the column we're interested in:

   SELECT t2.tag_id
   FROM taggings AS t1
   JOIN taggings AS t2
   WHERE t1.tag_id = 38 AND t1.taggable_id = t2.taggable_id AND t1.taggable_type = t2.taggable_type
   GROUP BY t2.tag_id;

   Now let's JOIN the tags table at the same time so that we can get the tag names at the same time:

   SELECT t2.tag_id, tags.name
   FROM tags
   JOIN taggings AS t1
   JOIN taggings AS t2
   WHERE t1.tag_id = 38
   AND t1.taggable_id = t2.taggable_id
   AND t1.taggable_type = t2.taggable_type
   AND tags.id = t2.tag_id
   GROUP BY t2.tag_id;

   Not sure if this is right, but it produces the expected results on my small test case with only one tag. Will need to think about how this could be extending to handle multiple tags.

3. Greg Hurrell 2009-01-28T06:28:23Z

   "Drilling down" gets a bit ugly.

   It's not enough to just do a t1.tag_id = 38 OR t1.tag_id = 40, nor a t1.tag_id IN (38, 40) because those won't actually narrow down the scope and the exact same results will be returned.

   Likewise t1.tag_id = 38 AND t1.tag_id = 40 won't work for obvious reasons.

   Basically, we need to do one more self-JOIN for each level that we wish to drill down. Evidently it will make sense to impose a limit on the number of joins we'll do although in practical terms most items have only 1 or 2 tags on them and it's hard to imagine drilling down more than 5 levels (I think the most heavily tagged item I have right now probably only has 3 or 4 tags on it).

   Here's an example with one additional level of drilling:

   SELECT t3.tag_id
   FROM taggings AS t1
   JOIN taggings AS t2
   JOIN taggings AS t3
   WHERE t1.tag_id = 38
   AND t2.tag_id = 40
   AND t1.taggable_id = t2.taggable_id
   AND t1.taggable_type = t2.taggable_type
   AND t3.taggable_id = t1.taggable_id
   AND t3.taggable_type = t1.taggable_type
   GROUP BY t3.tag_id;

   The same with the tags tabled JOIN-ed in so as to fetch the tag name at the same time:

   SELECT t3.tag_id, tags.name
   FROM tags, taggings AS t1
   JOIN taggings AS t2
   JOIN taggings AS t3
   WHERE t1.tag_id = 38
   AND t2.tag_id = 40
   AND t1.taggable_id = t2.taggable_id
   AND t1.taggable_type = t2.taggable_type
   AND t3.taggable_id = t1.taggable_id
   AND t3.taggable_type = t1.taggable_type
   AND tags.id = t3.tag_id
   GROUP BY t3.tag_id;

   And while we're at it we should probably exclude the passed in tags from the results list:

    SELECT t3.tag_id, tags.name
   FROM tags, taggings AS t1
   JOIN taggings AS t2
   JOIN taggings AS t3
   WHERE t1.tag_id = 38
   AND t2.tag_id = 40
   AND t1.taggable_id = t2.taggable_id
   AND t1.taggable_type = t2.taggable_type
   AND t3.taggable_id = t1.taggable_id
   AND t3.taggable_type = t1.taggable_type
   AND tags.id = t3.tag_id
   AND t3.tag_id NOT IN (38, 40)
   GROUP BY t3.tag_id;

4. Greg Hurrell 2009-01-28T06:39:58Z

   For the purposes of illustration, here's the "first level" example again, this time with the passed-i tag excluded and including the "tagging count":

   SELECT t2.tag_id, tags.name, tags.taggings_count
   FROM tags
   JOIN taggings AS t1
   JOIN taggings AS t2
   WHERE t1.tag_id = 38
   AND t1.taggable_id = t2.taggable_id
   AND t1.taggable_type = t2.taggable_type
   AND tags.id = t2.tag_id
   AND t2.tag_id NOT IN (38)
   GROUP BY t2.tag_id;

5. Greg Hurrell 2009-01-30T15:40:53Z

   Ok, this is now implemented and I'm reasonably happy with the result. Will be included in the next deployment.

6. Greg Hurrell 2009-02-03T05:42:47Z

   Status changed:

   • From: Open
   • To: Closed

Add a comment

Comments are now closed for this issue.