Changeset 20 in bookmarks for trunk/Bookmarks.pm

Timestamp:

05/21/13 17:55:34 (11 years ago)

Author:

peter

Message:

• Bookmarks::get_resources() and Bookmarks::get_cotags() both accept arrayrefs as their tag parameters
• can now use multiple ?tag query parameters in the request and it will search for the intersection of those tags
• bkmk list takes 0 or more --tag command line options
• display a checkbox-based form listing the search tags and available cotags at the below the list of bookmarks

File:

• trunk/Bookmarks.pm (modified) (2 diffs)

trunk/Bookmarks.pm

@@ -53 +53 @@
     my $self = shift;
     my $params = shift;
-    my $tag = $params->{tag};
+    my $tags = $params->{tag} || [];
     my $limit = $params->{limit};
     my $offset = $params->{offset};
 
-    my ($sql, @bind) = sql_interp(
-        'select * from resources join bookmarks on resources.uri = bookmarks.uri',
-        ($tag   ? ('join tags on resources.uri = tags.uri where tags.tag =', \$tag) : ''),
-        'order by ctime desc',
-        ($limit ? ('limit', \$limit) : ()),
-        # an offset is only allowed if we have a limit clause
-        ($limit && $offset ? ('offset', \$offset) : ()),
-    );
+    # build the query
+    my @sql;
+
+    if (!ref $tags) {
+        $tags = [ $tags ];
+    }
+    if (@$tags) {
+        my $intersect = 0;
+        for my $tag (@{ $tags }) {
+            push @sql, 'intersect' if $intersect;
+            push @sql, 'select resources.*, bookmarks.* from resources join bookmarks on resources.uri = bookmarks.uri';
+            push @sql, 'join tags on resources.uri = tags.uri where tags.tag =', \$tag;
+            $intersect++;
+        }
+    } else {
+        push @sql, 'select * from resources join bookmarks on resources.uri = bookmarks.uri';
+    }
+    push @sql, 'order by ctime desc';
+    push @sql, ('limit', \$limit) if $limit;
+    # an offset is only allowed if we have a limit clause
+    push @sql, ('offset', \$offset) if $limit && $offset;
+
+    my ($sql, @bind) = sql_interp(@sql);
 
     my $sth_resource = $self->dbh->prepare($sql);
@@ -95 +110 @@
     my $self = shift;
     my $params = shift;
-    my $tag = $params->{tag};
-    my $sth = $self->dbh->prepare('select tag, count(tag) as count from tags where tag != ? and uri in (select uri from tags where tag = ?) group by tag order by tag');
-    $sth->execute($tag, $tag);
+    my $tags = $params->{tag} || [];
+    if (!ref $tags) {
+        $tags = [ $tags ];
+    }
+    my @sql;
+
+    push @sql, 'select tag, count(tag) as count from tags';
+    if (@$tags) {
+        push @sql, 'where uri in (';
+        my $intersect = 0;
+        for my $tag (@{ $tags }) {
+            push @sql, 'intersect' if $intersect;
+            push @sql, 'select uri from tags where tag = ', \$tag;
+            $intersect++;
+        }
+        push @sql, ') and tag not in ', $tags, '';
+    }
+    push @sql, 'group by tag order by tag';
+
+    my ($sql, @bind) = sql_interp(@sql);
+    my $sth = $self->dbh->prepare($sql);
+    $sth->execute(@bind);
     return @{ $sth->fetchall_arrayref({}) };
 }