add new method for better sql performance: Bookmark2Tag::getTagsForBookmarks()

git-svn-id: https://semanticscuttle.svn.sourceforge.net/svnroot/semanticscuttle/trunk@667 b3834d28-1941-0410-a4f8-b48e95affb8f

2 files changed, 121 insertions, 0 deletions

diff --git a/src/SemanticScuttle/Service/Bookmark2Tag.php b/src/SemanticScuttle/Service/Bookmark2Tag.php
index e0f831e..770b1d9 100644
--- a/src/SemanticScuttle/Service/Bookmark2Tag.php
+++ b/src/SemanticScuttle/Service/Bookmark2Tag.php
@@ -305,6 +305,54 @@ class SemanticScuttle_Service_Bookmark2Tag extends SemanticScuttle_DbService
         return $tags;
     }
 
+
+    /**
+     * Retrieves all tags for an array of bookmark IDs
+     *
+     * @param array $bookmarkids Array of bookmark IDs
+     *
+     * @return array Array of tag arrays. Key is bookmark ID.
+     */
+    public function getTagsForBookmarks($bookmarkids)
+    {
+        if (!is_array($bookmarkids)) {
+            message_die(
+                GENERAL_ERROR, 'Could not get tags (invalid bookmarkids)',
+                '', __LINE__, __FILE__, $query
+            );
+            return false;
+        }
+
+        $sql = '';
+        foreach ($bookmarkids as $bookmarkid) {
+            $sql .= ' OR bId = ' . intval($bookmarkid);
+        }
+
+        $query = 'SELECT tag, bId FROM ' . $this->getTableName()
+            . ' WHERE (1' . $sql . ')'
+            . ' AND LEFT(tag, 7) <> "system:"'
+            . ' ORDER BY id, bId ASC';
+
+        if (!($dbresult = $this->db->sql_query($query))) {
+            message_die(
+                GENERAL_ERROR, 'Could not get tags',
+                '', __LINE__, __FILE__, $query, $this->db
+            );
+            return false;
+        }
+
+        $tags = array_combine(
+            $bookmarkids,
+            array_fill(0, count($bookmarkids), array())
+        );
+        while ($row = $this->db->sql_fetchrow($dbresult)) {
+            $tags[$row['bId']][] = $row['tag'];
+        }
+        $this->db->sql_freeresult($dbresult);
+        return $tags;
+    }
+
+
     function &getTags($userid = NULL) {
         $userservice =SemanticScuttle_Service_Factory::get('User');
         $logged_on_user = $userservice->getCurrentUserId();
diff --git a/tests/Bookmark2TagTest.php b/tests/Bookmark2TagTest.php
index d75afd8..0afaaf8 100644
--- a/tests/Bookmark2TagTest.php
+++ b/tests/Bookmark2TagTest.php
@@ -126,6 +126,79 @@ class Bookmark2TagTest extends TestBase
         $this->assertContains('bar', $tags);
         $this->assertContains('fuu', $tags);
     }
+
+
+
+    /**
+     * Test getTagsForBookmarks() when no bookmarks have tags.
+     *
+     * @return void
+     */
+    public function testGetTagsForBookmarksNone()
+    {
+        $bid1 = $this->addBookmark(null, null, 0, array());
+        $bid2 = $this->addBookmark(null, null, 0, array());
+
+        $alltags = $this->b2ts->getTagsForBookmarks(
+            array($bid1, $bid2)
+        );
+        $this->assertType('array', $alltags);
+        $this->assertEquals(2, count($alltags));
+        $this->assertType('array', $alltags[$bid1]);
+        $this->assertType('array', $alltags[$bid2]);
+        $this->assertEquals(0, count($alltags[$bid1]));
+        $this->assertEquals(0, count($alltags[$bid2]));
+    }
+
+
+
+    /**
+     * Test getTagsForBookmarks() when most bookmarks have tags.
+     *
+     * @return void
+     */
+    public function testGetTagsForBookmarksMost()
+    {
+        $bid1 = $this->addBookmark(null, null, 0, array());
+        $this->b2ts->attachTags($bid1, array('foo', 'bar', 'fuu'));
+
+        $bid2 = $this->addBookmark(null, null, 0, array());
+        $this->b2ts->attachTags($bid2, array('foo', 'bar2', 'fuu2'));
+
+        $bid3 = $this->addBookmark(null, null, 0, array());
+        $this->b2ts->attachTags($bid3, array('foo', 'bar2', 'fuu3'));
+
+        $bid4 = $this->addBookmark(null, null, 0, array());
+        //no tags
+
+        $alltags = $this->b2ts->getTagsForBookmarks(
+            array($bid1, $bid2, $bid3, $bid4)
+        );
+        $this->assertType('array', $alltags);
+        foreach ($alltags as $bid => $btags) {
+            $this->assertType('array', $btags);
+            if ($bid == $bid1) {
+                $this->assertEquals(3, count($btags));
+                $this->assertContains('foo', $btags);
+                $this->assertContains('bar', $btags);
+                $this->assertContains('fuu', $btags);
+            } else if ($bid == $bid2) {
+                $this->assertEquals(3, count($btags));
+                $this->assertContains('foo', $btags);
+                $this->assertContains('bar2', $btags);
+                $this->assertContains('fuu2', $btags);
+            } else if ($bid == $bid3) {
+                $this->assertEquals(3, count($btags));
+                $this->assertContains('foo', $btags);
+                $this->assertContains('bar2', $btags);
+                $this->assertContains('fuu3', $btags);
+            } else if ($bid == $bid4) {
+                $this->assertEquals(0, count($btags));
+            } else {
+                $this->assertTrue(false, 'Unknown bookmark id');
+            }
+        }
+    }
 }
 
 if (PHPUnit_MAIN_METHOD == 'Bookmark2TagTest::main') {