- 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,
- ))
+ WITH AllTagged AS (''' + rel_sql + ''')
+ SELECT catalogue_tag.*, COUNT(catalogue_tag.id) AS count
+ FROM catalogue_tag, AllTagged
+ WHERE catalogue_tag.id=AllTagged.tag_id
+ AND catalogue_tag.category IN %s
+ AND NOT EXISTS (
+ SELECT AncestorTagged.id
+ FROM catalogue_book_ancestor Ancestor,
+ AllTagged AncestorTagged
+ WHERE Ancestor.from_book_id=AllTagged.object_id
+ AND AncestorTagged.content_type_id=%s
+ AND AncestorTagged.object_id=Ancestor.to_book_id
+ AND AncestorTagged.tag_id=AllTagged.tag_id
+ )
+ GROUP BY catalogue_tag.id
+ ORDER BY sort_key''', rel_params + (categories, bct.pk))