--- /dev/null
+from django.db import connection
+from django.contrib.contenttypes.models import ContentType
+from django.utils.translation import get_language
+from picture.models import Picture, PictureArea
+from catalogue.models import Fragment, Tag, Book
+
+
+def _get_tag_relations_sql(tags):
+ select = """
+ SELECT Rx.object_id, Rx.content_type_id
+ FROM catalogue_tag_relation Rx"""
+ joins = []
+ where = ['WHERE Rx.tag_id = %d' % tags[0].pk]
+ for i, tag in enumerate(tags[1:]):
+ joins.append('INNER JOIN catalogue_tag_relation TR%(i)d '
+ 'ON TR%(i)d.object_id = Rx.object_id '
+ 'AND TR%(i)d.content_type_id = Rx.content_type_id' % {'i': i})
+ where.append('AND TR%d.tag_id = %d' % (i, tag.pk))
+ return " ".join([select] + joins + where)
+
+
+
+def get_related_tags(tags):
+ # Get Tag fields for constructing tags in a raw query.
+ tag_fields = ('id', 'category', 'slug', 'sort_key', 'name_%s' % get_language())
+ tag_fields = ', '.join(
+ 'T.%s' % connection.ops.quote_name(field)
+ for field in tag_fields)
+ tag_ids = tuple(t.pk for t in tags)
+
+ # This is based on fragments/areas sharing their works tags
+ qs = Tag.objects.raw('''
+ SELECT ''' + tag_fields + ''', COUNT(T.id) count
+ FROM (
+ -- R: TagRelations of all objects tagged with the given tags.
+ WITH R AS (
+ ''' + _get_tag_relations_sql(tags) + '''
+ )
+
+ SELECT ''' + tag_fields + ''', MAX(R4.object_id) ancestor
+
+ FROM R R1
+
+ -- R2: All tags of the found objects.
+ JOIN catalogue_tag_relation R2
+ ON R2.object_id = R1.object_id
+ AND R2.content_type_id = R1.content_type_id
+
+ -- Tag data for output.
+ JOIN catalogue_tag T
+ ON T.id=R2.tag_id
+
+ -- Special case for books:
+ -- We want to exclude from output all the relations
+ -- between a book and a tag, if there's a relation between
+ -- the the book's ancestor and the tag in the result.
+ LEFT JOIN catalogue_book_ancestor A
+ ON A.from_book_id = R1.object_id
+ AND R1.content_type_id = %s
+ LEFT JOIN catalogue_tag_relation R3
+ ON R3.tag_id = R2.tag_id
+ AND R3.content_type_id = R1.content_type_id
+ AND R3.object_id = A.to_book_id
+ LEFT JOIN R R4
+ ON R4.object_id = R3.object_id
+ AND R4.content_type_id = R3.content_type_id
+
+ WHERE
+ -- Exclude from the result the tags we started with.
+ R2.tag_id NOT IN %s
+ -- Special case for books: exclude descendants.
+ -- AND R4.object_id IS NULL
+ AND (
+ -- Only count fragment tags on fragments
+ -- and book tags for books.
+ (R2.content_type_id IN %s AND T.category IN %s)
+ OR
+ (R2.content_type_id IN %s AND T.category IN %s)
+ )
+
+ GROUP BY T.id, R2.object_id, R2.content_type_id
+
+ ) T
+ -- Now group by tag and count occurencies.
+ WHERE ancestor IS NULL
+ GROUP BY ''' + tag_fields + '''
+ ORDER BY T.sort_key
+ ''', params=(
+ ContentType.objects.get_for_model(Book).pk,
+ tag_ids,
+ tuple(ContentType.objects.get_for_model(model).pk
+ for model in (Fragment, PictureArea)),
+ ('theme', 'object'),
+ tuple(ContentType.objects.get_for_model(model).pk
+ for model in (Book, Picture)),
+ ('author', 'epoch', 'genre', 'kind'),
+ ))
+ return qs
+
+
+def get_fragment_related_tags(tags):
+ tag_fields = ', '.join(
+ 'T.%s' % (connection.ops.quote_name(field.column))
+ for field in Tag._meta.fields)
+
+ tag_ids = tuple(t.pk for t in tags)
+ # This is based on fragments/areas sharing their works tags
+ return Tag.objects.raw('''
+ SELECT T.*, COUNT(T.id) count
+ FROM (
+
+ SELECT T.*
+
+ -- R1: TagRelations of all objects tagged with the given tags.
+ FROM (
+ ''' + _get_tag_relations_sql(tags) + '''
+ ) R1
+
+ -- R2: All tags of the found objects.
+ JOIN catalogue_tag_relation R2
+ ON R2.object_id = R1.object_id
+ AND R2.content_type_id = R1.content_type_id
+
+ -- Tag data for output.
+ JOIN catalogue_tag T
+ ON T.id = R2.tag_id
+
+ WHERE
+ -- Exclude from the result the tags we started with.
+ R2.tag_id NOT IN %s
+ GROUP BY T.id, R2.object_id, R2.content_type_id
+
+ ) T
+ -- Now group by tag and count occurencies.
+ GROUP BY ''' + tag_fields + '''
+ ORDER BY T.sort_key
+ ''', params=(
+ tag_ids,
+ ))
+
+
+def tags_usage_for_books(categories):
+ tag_fields = ', '.join(
+ 'T.%s' % (connection.ops.quote_name(field.column))
+ for field in Tag._meta.fields)
+
+ # This is based on fragments/areas sharing their works tags
+ return Tag.objects.raw('''
+ SELECT T.*, COUNT(T.id) count
+ FROM (
+ SELECT T.*
+
+ FROM catalogue_tag_relation R1
+
+ -- Tag data for output.
+ JOIN catalogue_tag T
+ ON T.id=R1.tag_id
+
+ -- We want to exclude from output all the relations
+ -- between a book and a tag, if there's a relation between
+ -- the the book's ancestor and the tag in the result.
+ LEFT JOIN catalogue_book_ancestor A
+ ON A.from_book_id=R1.object_id
+ LEFT JOIN catalogue_tag_relation R3
+ ON R3.tag_id = R1.tag_id
+ AND R3.content_type_id = R1.content_type_id
+ AND R3.object_id = A.to_book_id
+
+ WHERE
+ R1.content_type_id = %s
+ -- Special case for books: exclude descendants.
+ AND R3.object_id IS NULL
+ AND T.category IN %s
+
+ -- TODO:
+ -- Shouldn't it just be 'distinct'?
+ -- Maybe it's faster this way.
+ GROUP BY T.id, R1.object_id, R1.content_type_id
+
+ ) T
+ -- Now group by tag and count occurencies.
+ GROUP BY ''' + tag_fields + '''
+ ORDER BY T.sort_key
+ ''', params=(
+ ContentType.objects.get_for_model(Book).pk,
+ tuple(categories),
+ ))
+
+
+def tags_usage_for_works(categories):
+ tag_fields = ', '.join(
+ 'T.%s' % (connection.ops.quote_name(field.column))
+ for field in Tag._meta.fields)
+
+ return Tag.objects.raw('''
+ SELECT T.*, COUNT(T.id) count
+ FROM (
+
+ SELECT T.*
+
+ FROM catalogue_tag_relation R1
+
+ -- Tag data for output.
+ JOIN catalogue_tag T
+ ON T.id = R1.tag_id
+
+ -- Special case for books:
+ -- We want to exclude from output all the relations
+ -- between a book and a tag, if there's a relation between
+ -- the the book's ancestor and the tag in the result.
+ LEFT JOIN catalogue_book_ancestor A
+ ON A.from_book_id = R1.object_id
+ AND R1.content_type_id = %s
+ LEFT JOIN catalogue_tag_relation R3
+ ON R3.tag_id = R1.tag_id
+ AND R3.content_type_id = R1.content_type_id
+ AND R3.object_id = A.to_book_id
+
+ WHERE
+ R1.content_type_id IN %s
+ -- Special case for books: exclude descendants.
+ AND R3.object_id IS NULL
+ AND T.category IN %s
+
+ -- TODO:
+ -- Shouldn't it just be 'distinct'?
+ -- Maybe it's faster this way.
+ GROUP BY T.id, R1.object_id, R1.content_type_id
+
+ ) T
+ -- Now group by tag and count occurencies.
+ GROUP BY ''' + tag_fields + '''
+ ORDER BY T.sort_key
+
+ ''', params=(
+ ContentType.objects.get_for_model(Book).pk,
+ tuple(ContentType.objects.get_for_model(model).pk for model in (Book, Picture)),
+ categories,
+ ))
+
+
+def tags_usage_for_fragments(categories):
+ return Tag.objects.raw('''
+ SELECT t.*, count(t.id)
+ from catalogue_tag_relation r
+ join catalogue_tag t
+ on t.id = r.tag_id
+ where t.category IN %s
+ group by t.id
+ order by t.sort_key
+ ''', params=(
+ categories,
+ ))
fnp / wolnelektury.git / blobdiff