From 9c460c6db7e6a4e7f8ed3e8d93032c7ef070efee Mon Sep 17 00:00:00 2001 From: Wolfgang Müller Date: Thu, 20 Feb 2025 13:40:40 +0100 Subject: Add filter for association counts This will replace the old 'empty' filter on comic associations and introduce a generic way of matching against association counts, along with support for different operators like 'greater than' or 'lower than'. Models that did not previously have a way of matching against their associates (like filtering for Artists that have N comics associated with them) now gain that functionality. For now the frontend keeps the simpler approach of allowing the user to only filter for empty associations, but we nonetheless need to adjust the 'empty' field to instead be linked to the new 'count' field. --- frontend/src/gql/graphql.ts | 25 +++++++++- frontend/src/lib/Enums.ts | 7 +++ frontend/src/lib/Filter.svelte.ts | 25 +++++++--- src/hircine/api/filters.py | 53 ++++++++++++++++----- src/hircine/db/models.py | 21 ++++++++- src/hircine/enums.py | 8 ++++ tests/api/test_filter.py | 99 ++++++++++++++++++++++++++++++--------- 7 files changed, 194 insertions(+), 44 deletions(-) diff --git a/frontend/src/gql/graphql.ts b/frontend/src/gql/graphql.ts index bd001f3..63f2c55 100644 --- a/frontend/src/gql/graphql.ts +++ b/frontend/src/gql/graphql.ts @@ -122,6 +122,7 @@ export type Artist = { }; export type ArtistFilter = { + comics?: InputMaybe; name?: InputMaybe; }; @@ -164,10 +165,14 @@ export type ArtistsUpsertInput = { export type AssociationFilter = { all?: InputMaybe>; any?: InputMaybe>; - empty?: InputMaybe; + count?: InputMaybe; exact?: InputMaybe>; }; +export type BasicCountFilter = { + count: CountFilter; +}; + export enum Category { Artbook = 'ARTBOOK', Comic = 'COMIC', @@ -203,6 +208,7 @@ export type Character = { }; export type CharacterFilter = { + comics?: InputMaybe; name?: InputMaybe; }; @@ -249,6 +255,7 @@ export type Circle = { }; export type CircleFilter = { + comics?: InputMaybe; name?: InputMaybe; }; @@ -396,6 +403,11 @@ export type ComicTotals = { worlds: Scalars['Int']['output']; }; +export type CountFilter = { + operator?: InputMaybe; + value: Scalars['Int']['input']; +}; + export type CoverInput = { id: Scalars['Int']['input']; }; @@ -862,6 +874,7 @@ export type Namespace = { export type NamespaceFilter = { name?: InputMaybe; + tags?: InputMaybe; }; export type NamespaceFilterInput = { @@ -905,6 +918,12 @@ export enum OnMissing { Ignore = 'IGNORE' } +export enum Operator { + Equal = 'EQUAL', + GreaterThan = 'GREATER_THAN', + LowerThan = 'LOWER_THAN' +} + export type Page = { __typename?: 'Page'; comicId?: Maybe; @@ -1153,11 +1172,12 @@ export type Tag = { export type TagAssociationFilter = { all?: InputMaybe>; any?: InputMaybe>; - empty?: InputMaybe; + count?: InputMaybe; exact?: InputMaybe>; }; export type TagFilter = { + comics?: InputMaybe; name?: InputMaybe; namespaces?: InputMaybe; }; @@ -1324,6 +1344,7 @@ export type World = { }; export type WorldFilter = { + comics?: InputMaybe; name?: InputMaybe; }; diff --git a/frontend/src/lib/Enums.ts b/frontend/src/lib/Enums.ts index 3264de4..db9fb86 100644 --- a/frontend/src/lib/Enums.ts +++ b/frontend/src/lib/Enums.ts @@ -10,6 +10,7 @@ import { Language, Layout, NamespaceSort, + Operator, Rating, TagSort, UpdateMode, @@ -125,6 +126,12 @@ export const UpdateModeLabel: Record = { [UpdateMode.Replace]: 'Replace' }; +export const OperatorLabel: Record = { + [Operator.Equal]: 'Equal', + [Operator.GreaterThan]: 'Greater than', + [Operator.LowerThan]: 'Lower than,' +}; + export const LanguageLabel: Record = { [Language.Ab]: 'Abkhazian', [Language.Aa]: 'Afar', diff --git a/frontend/src/lib/Filter.svelte.ts b/frontend/src/lib/Filter.svelte.ts index 6183f06..e73f497 100644 --- a/frontend/src/lib/Filter.svelte.ts +++ b/frontend/src/lib/Filter.svelte.ts @@ -1,4 +1,5 @@ import { + Operator, type ArchiveFilter, type ArchiveFilterInput, type ComicFilter, @@ -30,7 +31,7 @@ type AssocFilter = Filter< any?: T[] | null; all?: T[] | null; exact?: T[] | null; - empty?: boolean | null; + count?: { value: number; operator?: Operator | null } | null; }, K >; @@ -62,10 +63,6 @@ class ComplexMember { if (this.values.length > 0) { filter[this.key] = { [this.mode]: this.values }; } - - if (this.empty) { - filter[this.key] = { ...filter[this.key], empty: this.empty }; - } } } @@ -80,7 +77,9 @@ export class Association extends ComplexMember { } const prop = filter[key]; - this.empty = prop?.empty; + this.empty = + prop?.count?.value === 0 && + (prop.count.operator === undefined || prop.count.operator === Operator.Equal); if (prop?.all && prop.all.length > 0) { this.mode = 'all'; @@ -93,6 +92,13 @@ export class Association extends ComplexMember { this.values = prop.exact; } } + + integrate(filter: AssocFilter) { + super.integrate(filter); + if (this.empty) { + filter[this.key] = { ...filter[this.key], count: { value: 0, operator: Operator.Equal } }; + } + } } export class Enum extends ComplexMember { @@ -112,6 +118,13 @@ export class Enum extends ComplexMember { this.values = prop.any; } } + + integrate(filter: EnumFilter) { + super.integrate(filter); + if (this.empty) { + filter[this.key] = { ...filter[this.key], empty: this.empty }; + } + } } class Bool { diff --git a/src/hircine/api/filters.py b/src/hircine/api/filters.py index 807178b..7ed5649 100644 --- a/src/hircine/api/filters.py +++ b/src/hircine/api/filters.py @@ -7,7 +7,7 @@ from strawberry import UNSET import hircine.db from hircine.db.models import ComicTag -from hircine.enums import Category, Censorship, Language, Rating +from hircine.enums import Category, Censorship, Language, Operator, Rating T = TypeVar("T") @@ -27,12 +27,24 @@ class Matchable(ABC): return sql +@strawberry.input +class CountFilter: + operator: Optional[Operator] = Operator.EQUAL + value: int + + def include(self, column, sql): + return sql.where(self.operator.value(column, self.value)) + + def exclude(self, column, sql): + return sql.where(~self.operator.value(column, self.value)) + + @strawberry.input class AssociationFilter(Matchable): any: Optional[list[int]] = strawberry.field(default_factory=lambda: None) all: Optional[list[int]] = strawberry.field(default_factory=lambda: None) exact: Optional[list[int]] = strawberry.field(default_factory=lambda: None) - empty: Optional[bool] = None + count: Optional[CountFilter] = UNSET def _exists(self, condition): # The property.primaryjoin expression specifies the primary join path @@ -71,12 +83,6 @@ class AssociationFilter(Matchable): def _where_not_all_exist(self, sql): return sql.where(~self._all_exist(self.all)) - def _empty(self): - if self.empty: - return ~self._exists(True) - else: - return self._exists(True) - def _count_of(self, column): return ( select(func.count(column)) @@ -117,8 +123,8 @@ class AssociationFilter(Matchable): elif self.all == []: sql = sql.where(False) - if self.empty is not None: - sql = sql.where(self._empty()) + if self.count: + sql = self.count.include(self.count_column, sql) if self.exact is not None: sql = sql.where(self._exact()) @@ -134,8 +140,8 @@ class AssociationFilter(Matchable): if self.all: sql = self._where_not_all_exist(sql) - if self.empty is not None: - sql = sql.where(~self._empty()) + if self.count: + sql = self.count.exclude(self.count_column, sql) if self.exact is not None: sql = sql.where(~self._exact()) @@ -160,8 +166,14 @@ class Root: column = getattr(self._model, field, None) + # count columns are historically singular, so we need this hack + singular_field = field[:-1] + count_column = getattr(self._model, f"{singular_field}_count", None) + if issubclass(type(matcher), Matchable): matcher.column = column + matcher.count_column = count_column + if not negate: sql = matcher.include(sql) else: @@ -212,6 +224,17 @@ class StringFilter(Matchable): return sql.where(and_(*conditions)) +@strawberry.input +class BasicCountFilter(Matchable): + count: CountFilter + + def include(self, sql): + return self.count.include(self.count_column, sql) + + def exclude(self, sql): + return self.count.exclude(self.count_column, sql) + + @strawberry.input class TagAssociationFilter(AssociationFilter): """ @@ -314,24 +337,28 @@ class ArchiveFilter(Root): @strawberry.input class ArtistFilter(Root): name: Optional[StringFilter] = UNSET + comics: Optional[BasicCountFilter] = UNSET @hircine.db.model("Character") @strawberry.input class CharacterFilter(Root): name: Optional[StringFilter] = UNSET + comics: Optional[BasicCountFilter] = UNSET @hircine.db.model("Circle") @strawberry.input class CircleFilter(Root): name: Optional[StringFilter] = UNSET + comics: Optional[BasicCountFilter] = UNSET @hircine.db.model("Namespace") @strawberry.input class NamespaceFilter(Root): name: Optional[StringFilter] = UNSET + tags: Optional[BasicCountFilter] = UNSET @hircine.db.model("Tag") @@ -339,9 +366,11 @@ class NamespaceFilter(Root): class TagFilter(Root): name: Optional[StringFilter] = UNSET namespaces: Optional[AssociationFilter] = UNSET + comics: Optional[BasicCountFilter] = UNSET @hircine.db.model("World") @strawberry.input class WorldFilter(Root): name: Optional[StringFilter] = UNSET + comics: Optional[BasicCountFilter] = UNSET diff --git a/src/hircine/db/models.py b/src/hircine/db/models.py index f204998..5d1a59a 100644 --- a/src/hircine/db/models.py +++ b/src/hircine/db/models.py @@ -356,7 +356,10 @@ class ComicWorld(Base): def defer_relationship_count(relationship, secondary=False): - left, right = relationship.property.synchronize_pairs[0] + if secondary: + left, right = relationship.property.secondary_synchronize_pairs[0] + else: + left, right = relationship.property.synchronize_pairs[0] return deferred( select(func.count(right)) @@ -366,7 +369,23 @@ def defer_relationship_count(relationship, secondary=False): ) +Comic.artist_count = defer_relationship_count(Comic.artists) +Comic.character_count = defer_relationship_count(Comic.characters) +Comic.circle_count = defer_relationship_count(Comic.circles) Comic.tag_count = defer_relationship_count(Comic.tags) +Comic.world_count = defer_relationship_count(Comic.worlds) + +Artist.comic_count = defer_relationship_count(Comic.artists, secondary=True) +Character.comic_count = defer_relationship_count(Comic.characters, secondary=True) +Circle.comic_count = defer_relationship_count(Comic.circles, secondary=True) +Namespace.tag_count = defer_relationship_count(Tag.namespaces, secondary=True) +Tag.comic_count = deferred( + select(func.count(ComicTag.tag_id)) + .where(Tag.id == ComicTag.tag_id) + .scalar_subquery() +) +Tag.namespace_count = defer_relationship_count(Tag.namespaces) +World.comic_count = defer_relationship_count(Comic.worlds, secondary=True) @event.listens_for(Comic.pages, "bulk_replace") diff --git a/src/hircine/enums.py b/src/hircine/enums.py index 7f95f02..f267270 100644 --- a/src/hircine/enums.py +++ b/src/hircine/enums.py @@ -1,4 +1,5 @@ import enum +import operator import strawberry @@ -56,6 +57,13 @@ class OnMissing(enum.Enum): CREATE = "Create" +@strawberry.enum +class Operator(enum.Enum): + GREATER_THAN = operator.gt + LOWER_THAN = operator.lt + EQUAL = operator.eq + + @strawberry.enum class Language(enum.Enum): AA = "Afar" diff --git a/tests/api/test_filter.py b/tests/api/test_filter.py index 1438785..6eb2934 100644 --- a/tests/api/test_filter.py +++ b/tests/api/test_filter.py @@ -421,51 +421,59 @@ async def test_field_presence(query_comic_filter, gen_comic, empty_comic, filter "filter,ids", [ ( - {"include": {"artists": {"empty": True}}}, + {"include": {"artists": {"count": {"value": 0}}}}, [100], ), ( - {"include": {"artists": {"empty": False}}}, - [1, 2], + {"include": {"artists": {"count": {"value": 0, "operator": "EQUAL"}}}}, + [100], ), ( - {"exclude": {"artists": {"empty": True}}}, - [1, 2], + { + "include": { + "artists": {"count": {"value": 1, "operator": "GREATER_THAN"}} + } + }, + [1], ), ( - {"exclude": {"artists": {"empty": False}}}, - [100], + {"include": {"artists": {"count": {"value": 3, "operator": "LOWER_THAN"}}}}, + [1, 2, 100], ), ( - {"include": {"tags": {"empty": True}}}, - [100], + {"exclude": {"artists": {"count": {"value": 0}}}}, + [1, 2], ), ( - {"include": {"tags": {"empty": False}}}, + {"exclude": {"artists": {"count": {"value": 0, "operator": "EQUAL"}}}}, [1, 2], ), ( - {"exclude": {"tags": {"empty": True}}}, - [1, 2], + { + "exclude": { + "artists": {"count": {"value": 1, "operator": "GREATER_THAN"}} + } + }, + [2, 100], ), ( - {"exclude": {"tags": {"empty": False}}}, - [100], + {"exclude": {"artists": {"count": {"value": 3, "operator": "LOWER_THAN"}}}}, + [], ), ], ids=[ - "includes artist empty", - "includes artist not empty", - "excludes artist empty", - "excludes artist not empty", - "includes tags empty", - "includes tags not empty", - "excludes tags empty", - "excludes tags not empty", + "include equal (default)", + "include equal (explicit)", + "include greater than", + "include lower than", + "exclude equal (default)", + "exclude equal (explicit)", + "exclude greater than", + "exclude lower than", ], ) @pytest.mark.anyio -async def test_assoc_presence(query_comic_filter, gen_comic, empty_comic, filter, ids): +async def test_assoc_counts(query_comic_filter, gen_comic, empty_comic, filter, ids): await DB.add(next(gen_comic)) await DB.add(next(gen_comic)) await DB.add(empty_comic) @@ -520,3 +528,48 @@ async def test_tag_assoc_filter(query_tag_filter, gen_namespace, gen_tag, filter response.assert_is("TagFilterResult") assert id_list(response.edges) == ids + + +@pytest.mark.parametrize( + "filter,expect", + [ + ({"include": {"comics": {"count": {"value": 1}}}}, [2, 3]), + ({"include": {"comics": {"count": {"value": 2, "operator": "EQUAL"}}}}, [1, 4]), + ( + { + "include": { + "comics": {"count": {"value": 3, "operator": "GREATER_THAN"}} + } + }, + [], + ), + ( + {"include": {"comics": {"count": {"value": 2, "operator": "LOWER_THAN"}}}}, + [2, 3], + ), + ( + {"exclude": {"comics": {"count": {"value": 1}}}}, + [1, 4], + ), + ( + {"exclude": {"comics": {"count": {"value": 1, "operator": "LOWER_THAN"}}}}, + [1, 2, 3, 4], + ), + ], + ids=[ + "include equal (default)", + "include equal (explicit)", + "include greater than", + "include lower than", + "exclude equal (default)", + "exclude lower than", + ], +) +@pytest.mark.anyio +async def test_count_filter(query_string_filter, gen_comic, filter, expect): + await DB.add_all(*gen_comic) + + response = Response(await query_string_filter(filter)) + response.assert_is("ArtistFilterResult") + + assert id_list(response.edges) == expect -- cgit v1.2.3-2-gb3c3