serevr/tags: fix search by post count and category

3 files changed, 224 insertions, 80 deletions

diff --git a/server/szurubooru/db/tag_category.py b/server/szurubooru/db/tag_category.py
index d60965f..d82a7c5 100644
--- a/server/szurubooru/db/tag_category.py
+++ b/server/szurubooru/db/tag_category.py
@@ -17,4 +17,4 @@ class TagCategory(Base):
     tag_count = column_property(
         select([func.count('Tag.tag_id')]) \
         .where(Tag.category_id == tag_category_id) \
-        .correlate(table('TagCategory')))
+        .correlate_except(table('Tag')))
diff --git a/server/szurubooru/search/tag_search_config.py b/server/szurubooru/search/tag_search_config.py
index 3daa065..bea17f2 100644
--- a/server/szurubooru/search/tag_search_config.py
+++ b/server/szurubooru/search/tag_search_config.py
@@ -1,16 +1,19 @@
 from sqlalchemy.orm import joinedload
 from sqlalchemy.sql.expression import func
 from szurubooru import db
+from szurubooru.func import util
 from szurubooru.search.base_search_config import BaseSearchConfig
 
 class TagSearchConfig(BaseSearchConfig):
     def create_filter_query(self):
-        return self.create_count_query().options(
-            joinedload(db.Tag.names),
-            joinedload(db.Tag.category),
-            joinedload(db.Tag.suggestions).joinedload(db.Tag.names),
-            joinedload(db.Tag.implications).joinedload(db.Tag.names)
-        )
+        return self.create_count_query() \
+            .join(db.TagCategory) \
+            .options(
+                joinedload(db.Tag.names),
+                joinedload(db.Tag.category),
+                joinedload(db.Tag.suggestions).joinedload(db.Tag.names),
+                joinedload(db.Tag.implications).joinedload(db.Tag.names)
+            )
 
     def create_count_query(self):
         return db.session.query(db.Tag)
@@ -24,37 +27,35 @@ class TagSearchConfig(BaseSearchConfig):
 
     @property
     def named_filters(self):
-        return {
+        return util.unalias_dict({
             'name': self._create_str_filter(db.Tag.first_name),
-            'category': self._create_str_filter(db.Tag.category),
-            'creation-date': self._create_date_filter(db.Tag.creation_time),
-            'creation-time': self._create_date_filter(db.Tag.creation_time),
-            'last-edit-date': self._create_date_filter(db.Tag.last_edit_time),
-            'last-edit-time': self._create_date_filter(db.Tag.last_edit_time),
-            'edit-date': self._create_date_filter(db.Tag.last_edit_time),
-            'edit-time': self._create_date_filter(db.Tag.last_edit_time),
-            'usages': self._create_num_filter(db.Tag.post_count),
-            'usage-count': self._create_num_filter(db.Tag.post_count),
-            'post-count': self._create_num_filter(db.Tag.post_count),
+            'category': self._create_subquery_filter(
+                db.Tag.category_id,
+                db.TagCategory.tag_category_id,
+                db.TagCategory.name,
+                self._create_str_filter),
+            ('creation-date', 'creation-time'):
+                self._create_date_filter(db.Tag.creation_time),
+            ('last-edit-date', 'last-edit-time', 'edit-date', 'edit-time'):
+                self._create_date_filter(db.Tag.last_edit_time),
+            ('usage-count', 'post-count', 'usages'):
+                self._create_num_filter(db.Tag.post_count),
             'suggestion-count': self._create_num_filter(db.Tag.suggestion_count),
             'implication-count': self._create_num_filter(db.Tag.implication_count),
-        }
+        })
 
     @property
     def sort_columns(self):
-        return {
+        return util.unalias_dict({
             'random': (func.random(), None),
             'name': (db.Tag.first_name, self.SORT_ASC),
-            'category': (db.Tag.category, self.SORT_ASC),
-            'creation-date': (db.Tag.creation_time, self.SORT_DESC),
-            'creation-time': (db.Tag.creation_time, self.SORT_DESC),
-            'last-edit-date': (db.Tag.last_edit_time, self.SORT_DESC),
-            'last-edit-time': (db.Tag.last_edit_time, self.SORT_DESC),
-            'edit-date': (db.Tag.last_edit_time, self.SORT_DESC),
-            'edit-time': (db.Tag.last_edit_time, self.SORT_DESC),
-            'usages': (db.Tag.post_count, self.SORT_DESC),
-            'usage-count': (db.Tag.post_count, self.SORT_DESC),
-            'post-count': (db.Tag.post_count, self.SORT_DESC),
+            'category': (db.TagCategory.name, self.SORT_ASC),
+            ('creation-date', 'creation-time'):
+                (db.Tag.creation_time, self.SORT_DESC),
+            ('last-edit-date', 'last-edit-time', 'edit-date', 'edit-time'):
+                (db.Tag.last_edit_time, self.SORT_DESC),
+            ('usage-count', 'post-count', 'usages'):
+                (db.Tag.post_count, self.SORT_DESC),
             'suggestion-count': (db.Tag.suggestion_count, self.SORT_DESC),
             'implication-count': (db.Tag.implication_count, self.SORT_DESC),
-        }
+        })
diff --git a/server/szurubooru/tests/search/test_tag_search_config.py b/server/szurubooru/tests/search/test_tag_search_config.py
index 62e64b1..e03f038 100644
--- a/server/szurubooru/tests/search/test_tag_search_config.py
+++ b/server/szurubooru/tests/search/test_tag_search_config.py
@@ -18,6 +18,63 @@ def verify_unpaged(executor):
     return verify
 
 @pytest.mark.parametrize('input,expected_tag_names', [
+    ('', ['t1', 't2']),
+    ('t1', ['t1']),
+    ('t2', ['t2']),
+    ('t1,t2', ['t1', 't2']),
+])
+def test_filter_anonymous(verify_unpaged, tag_factory, input, expected_tag_names):
+    db.session.add(tag_factory(names=['t1']))
+    db.session.add(tag_factory(names=['t2']))
+    verify_unpaged(input, expected_tag_names)
+
+@pytest.mark.parametrize('input,expected_tag_names', [
+    ('name:tag1', ['tag1']),
+    ('name:tag2', ['tag2']),
+    ('name:none', []),
+    ('name:', []),
+    ('name:*1', ['tag1']),
+    ('name:*2', ['tag2']),
+    ('name:*', ['tag1', 'tag2', 'tag3', 'tag4']),
+    ('name:t*', ['tag1', 'tag2', 'tag3', 'tag4']),
+    ('name:*a*', ['tag1', 'tag2', 'tag3', 'tag4']),
+    ('name:*!*', []),
+    ('name:!*', []),
+    ('name:*!', []),
+    ('-name:tag1', ['tag2', 'tag3', 'tag4']),
+    ('-name:tag2', ['tag1', 'tag3', 'tag4']),
+    ('name:tag1,tag2', ['tag1', 'tag2']),
+    ('-name:tag1,tag3', ['tag2', 'tag4']),
+    ('name:tag4', ['tag4']),
+    ('name:tag4,tag5', ['tag4']),
+])
+def test_filter_by_name(verify_unpaged, tag_factory, input, expected_tag_names):
+    db.session.add(tag_factory(names=['tag1']))
+    db.session.add(tag_factory(names=['tag2']))
+    db.session.add(tag_factory(names=['tag3']))
+    db.session.add(tag_factory(names=['tag4', 'tag5', 'tag6']))
+    verify_unpaged(input, expected_tag_names)
+
+@pytest.mark.parametrize('input,expected_tag_names', [
+    ('category:cat1', ['t1', 't2']),
+    ('category:cat2', ['t3']),
+    ('category:cat1,cat2', ['t1', 't2', 't3']),
+])
+def test_filter_by_category(
+        verify_unpaged,
+        tag_factory,
+        tag_category_factory,
+        input,
+        expected_tag_names):
+    cat1 = tag_category_factory(name='cat1')
+    cat2 = tag_category_factory(name='cat2')
+    tag1 = tag_factory(names=['t1'], category=cat1)
+    tag2 = tag_factory(names=['t2'], category=cat1)
+    tag3 = tag_factory(names=['t3'], category=cat2)
+    db.session.add_all([tag1, tag2, tag3])
+    verify_unpaged(input, expected_tag_names)
+
+@pytest.mark.parametrize('input,expected_tag_names', [
     ('creation-time:2014', ['t1', 't2']),
     ('creation-date:2014', ['t1', 't2']),
     ('-creation-time:2014', ['t3']),
@@ -51,41 +108,79 @@ def test_filter_by_creation_time(
     verify_unpaged(input, expected_tag_names)
 
 @pytest.mark.parametrize('input,expected_tag_names', [
-    ('name:tag1', ['tag1']),
-    ('name:tag2', ['tag2']),
-    ('name:none', []),
-    ('name:', []),
-    ('name:*1', ['tag1']),
-    ('name:*2', ['tag2']),
-    ('name:*', ['tag1', 'tag2', 'tag3', 'tag4']),
-    ('name:t*', ['tag1', 'tag2', 'tag3', 'tag4']),
-    ('name:*a*', ['tag1', 'tag2', 'tag3', 'tag4']),
-    ('name:*!*', []),
-    ('name:!*', []),
-    ('name:*!', []),
-    ('-name:tag1', ['tag2', 'tag3', 'tag4']),
-    ('-name:tag2', ['tag1', 'tag3', 'tag4']),
-    ('name:tag1,tag2', ['tag1', 'tag2']),
-    ('-name:tag1,tag3', ['tag2', 'tag4']),
-    ('name:tag4', ['tag4']),
-    ('name:tag4,tag5', ['tag4']),
+    ('last-edit-date:2014', ['t1', 't3']),
+    ('last-edit-time:2014', ['t1', 't3']),
+    ('edit-date:2014', ['t1', 't3']),
+    ('edit-time:2014', ['t1', 't3']),
 ])
-def test_filter_by_name(verify_unpaged, tag_factory, input, expected_tag_names):
-    db.session.add(tag_factory(names=['tag1']))
-    db.session.add(tag_factory(names=['tag2']))
-    db.session.add(tag_factory(names=['tag3']))
-    db.session.add(tag_factory(names=['tag4', 'tag5', 'tag6']))
+def test_filter_by_edit_time(
+        verify_unpaged, tag_factory, input, expected_tag_names):
+    tag1 = tag_factory(names=['t1'])
+    tag2 = tag_factory(names=['t2'])
+    tag3 = tag_factory(names=['t3'])
+    tag1.last_edit_time = datetime.datetime(2014, 1, 1)
+    tag2.last_edit_time = datetime.datetime(2015, 1, 1)
+    tag3.last_edit_time = datetime.datetime(2014, 1, 1)
+    db.session.add_all([tag1, tag2, tag3])
     verify_unpaged(input, expected_tag_names)
 
 @pytest.mark.parametrize('input,expected_tag_names', [
-    ('', ['t1', 't2']),
-    ('t1', ['t1']),
-    ('t2', ['t2']),
-    ('t1,t2', ['t1', 't2']),
+    ('post-count:2', ['t1']),
+    ('post-count:1', ['t2']),
+    ('usage-count:2', ['t1']),
+    ('usage-count:1', ['t2']),
+    ('usages:2', ['t1']),
+    ('usages:1', ['t2']),
 ])
-def test_anonymous(verify_unpaged, tag_factory, input, expected_tag_names):
-    db.session.add(tag_factory(names=['t1']))
-    db.session.add(tag_factory(names=['t2']))
+def test_filter_by_post_count(
+        verify_unpaged, tag_factory, post_factory, input, expected_tag_names):
+    post1 = post_factory()
+    post2 = post_factory()
+    tag1 = tag_factory(names=['t1'])
+    tag2 = tag_factory(names=['t2'])
+    db.session.add_all([post1, post2, tag1, tag2])
+    db.session.commit()
+    post1.tags.append(tag1)
+    post1.tags.append(tag2)
+    post2.tags.append(tag1)
+    verify_unpaged(input, expected_tag_names)
+
+@pytest.mark.parametrize('input,expected_tag_names', [
+    ('suggestion-count:2', ['t1']),
+    ('suggestion-count:1', ['t2']),
+    ('suggestion-count:0', ['sug1', 'sug2', 'sug3']),
+])
+def test_filter_by_suggestion_count(
+        verify_unpaged, tag_factory, input, expected_tag_names):
+    sug1 = tag_factory(names=['sug1'])
+    sug2 = tag_factory(names=['sug2'])
+    sug3 = tag_factory(names=['sug3'])
+    tag1 = tag_factory(names=['t1'])
+    tag2 = tag_factory(names=['t2'])
+    db.session.add_all([sug1, sug3, tag2, sug2, tag1])
+    db.session.commit()
+    tag1.suggestions.append(sug1)
+    tag1.suggestions.append(sug2)
+    tag2.suggestions.append(sug3)
+    verify_unpaged(input, expected_tag_names)
+
+@pytest.mark.parametrize('input,expected_tag_names', [
+    ('implication-count:2', ['t1']),
+    ('implication-count:1', ['t2']),
+    ('implication-count:0', ['sug1', 'sug2', 'sug3']),
+])
+def test_filter_by_implication_count(
+        verify_unpaged, tag_factory, input, expected_tag_names):
+    sug1 = tag_factory(names=['sug1'])
+    sug2 = tag_factory(names=['sug2'])
+    sug3 = tag_factory(names=['sug3'])
+    tag1 = tag_factory(names=['t1'])
+    tag2 = tag_factory(names=['t2'])
+    db.session.add_all([sug1, sug3, tag2, sug2, tag1])
+    db.session.commit()
+    tag1.implications.append(sug1)
+    tag1.implications.append(sug2)
+    tag2.implications.append(sug3)
     verify_unpaged(input, expected_tag_names)
 
 @pytest.mark.parametrize('input,expected_tag_names', [
@@ -119,6 +214,42 @@ def test_sort_by_creation_time(
     verify_unpaged(input, expected_tag_names)
 
 @pytest.mark.parametrize('input,expected_tag_names', [
+    ('', ['t1', 't2', 't3']),
+    ('sort:last-edit-date', ['t3', 't2', 't1']),
+    ('sort:last-edit-time', ['t3', 't2', 't1']),
+    ('sort:edit-date', ['t3', 't2', 't1']),
+    ('sort:edit-time', ['t3', 't2', 't1']),
+])
+def test_sort_by_last_edit_time(
+        verify_unpaged, tag_factory, input, expected_tag_names):
+    tag1 = tag_factory(names=['t1'])
+    tag2 = tag_factory(names=['t2'])
+    tag3 = tag_factory(names=['t3'])
+    tag1.last_edit_time = datetime.datetime(1991, 1, 1)
+    tag2.last_edit_time = datetime.datetime(1991, 1, 2)
+    tag3.last_edit_time = datetime.datetime(1991, 1, 3)
+    db.session.add_all([tag3, tag1, tag2])
+    verify_unpaged(input, expected_tag_names)
+
+@pytest.mark.parametrize('input,expected_tag_names', [
+    ('sort:post-count', ['t2', 't1']),
+    ('sort:usage-count', ['t2', 't1']),
+    ('sort:usages', ['t2', 't1']),
+])
+def test_sort_by_post_count(
+        verify_unpaged, tag_factory, post_factory, input, expected_tag_names):
+    post1 = post_factory()
+    post2 = post_factory()
+    tag1 = tag_factory(names=['t1'])
+    tag2 = tag_factory(names=['t2'])
+    db.session.add_all([post1, post2, tag1, tag2])
+    db.session.commit()
+    post1.tags.append(tag1)
+    post1.tags.append(tag2)
+    post2.tags.append(tag2)
+    verify_unpaged(input, expected_tag_names)
+
+@pytest.mark.parametrize('input,expected_tag_names', [
     ('sort:suggestion-count', ['t1', 't2', 'sug1', 'sug2', 'sug3']),
 ])
 def test_sort_by_suggestion_count(
@@ -152,22 +283,34 @@ def test_sort_by_implication_count(
     tag2.implications.append(sug3)
     verify_unpaged(input, expected_tag_names)
 
-def test_filter_by_relation_count(verify_unpaged, tag_factory):
-    sug1 = tag_factory(names=['sug1'])
-    sug2 = tag_factory(names=['sug2'])
-    imp1 = tag_factory(names=['imp1'])
-    tag1 = tag_factory(names=['t1'])
-    tag2 = tag_factory(names=['t2'])
-    db.session.add_all([sug1, tag1, sug2, imp1, tag2])
-    db.session.commit()
-    db.session.add_all([
-        db.TagSuggestion(tag1.tag_id, sug1.tag_id),
-        db.TagSuggestion(tag1.tag_id, sug2.tag_id),
-        db.TagImplication(tag2.tag_id, imp1.tag_id)])
-    db.session.commit()
-    verify_unpaged('suggestion-count:0', ['imp1', 'sug1', 'sug2', 't2'])
-    verify_unpaged('suggestion-count:1', [])
-    verify_unpaged('suggestion-count:2', ['t1'])
-    verify_unpaged('implication-count:0', ['imp1', 'sug1', 'sug2', 't1'])
-    verify_unpaged('implication-count:1', ['t2'])
-    verify_unpaged('implication-count:2', [])
+@pytest.mark.parametrize('input,expected_tag_names', [
+    ('sort:category', ['t3', 't1', 't2']),
+])
+def test_sort_by_category(
+        verify_unpaged,
+        tag_factory,
+        tag_category_factory,
+        input,
+        expected_tag_names):
+    cat1 = tag_category_factory(name='cat1')
+    cat2 = tag_category_factory(name='cat2')
+    tag1 = tag_factory(names=['t1'], category=cat2)
+    tag2 = tag_factory(names=['t2'], category=cat2)
+    tag3 = tag_factory(names=['t3'], category=cat1)
+    db.session.add_all([tag1, tag2, tag3])
+    import sqlalchemy
+    from sqlalchemy.orm import joinedload
+    print('test', [tag.first_name for tag in db.session.query(db.Tag)
+        .join(db.TagCategory).options(
+            joinedload(db.Tag.names),
+            joinedload(db.Tag.category),
+            joinedload(db.Tag.suggestions).joinedload(db.Tag.names),
+            joinedload(db.Tag.implications).joinedload(db.Tag.names)
+        )
+        .options(sqlalchemy.orm.lazyload('*'))
+        .order_by(db.TagCategory.name.asc())
+        .order_by(db.Tag.first_name.asc())
+        .offset(0)
+        .limit(100)
+        .all()])
+    verify_unpaged(input, expected_tag_names)