# HG changeset patch # User ymh # Date 1495637153 -7200 # Node ID 6c879e963a93284155a8b62c85c61ba88c074976 # Parent 7f3fdcba790281056540523a31d5e2faa19d6c28 Optimization: reduce the total number of queries for home and collection home diff -r 7f3fdcba7902 -r 6c879e963a93 src/iconolab/migrations/0024_auto_20170524_0938.py --- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/src/iconolab/migrations/0024_auto_20170524_0938.py Wed May 24 16:45:53 2017 +0200 @@ -0,0 +1,31 @@ +# -*- coding: utf-8 -*- +# Generated by Django 1.10.5 on 2017-05-24 09:38 +from __future__ import unicode_literals + +from django.db import migrations, models +import django.db.models.deletion + + +class Migration(migrations.Migration): + + dependencies = [ + ('iconolab', '0023_auto_20170522_1011'), + ] + + operations = [ + migrations.AddField( + model_name='annotationstats', + name='accurate_tags_count', + field=models.IntegerField(blank=True, default=0, null=True), + ), + migrations.AddField( + model_name='annotationstats', + name='relevant_tags_count', + field=models.IntegerField(blank=True, default=0, null=True), + ), + migrations.AlterField( + model_name='metacategoriescountinfo', + name='annotation_stats_obj', + field=models.ForeignKey(on_delete=django.db.models.deletion.CASCADE, related_name='metacategoriescountinfos', to='iconolab.AnnotationStats'), + ), + ] diff -r 7f3fdcba7902 -r 6c879e963a93 src/iconolab/migrations/0025_annotationstats_contributors.py --- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/src/iconolab/migrations/0025_annotationstats_contributors.py Wed May 24 16:45:53 2017 +0200 @@ -0,0 +1,22 @@ +# -*- coding: utf-8 -*- +# Generated by Django 1.10.5 on 2017-05-24 11:06 +from __future__ import unicode_literals + +from django.conf import settings +from django.db import migrations, models + + +class Migration(migrations.Migration): + + dependencies = [ + migrations.swappable_dependency(settings.AUTH_USER_MODEL), + ('iconolab', '0024_auto_20170524_0938'), + ] + + operations = [ + migrations.AddField( + model_name='annotationstats', + name='contributors', + field=models.ManyToManyField(related_name='_annotationstats_contributors_+', to=settings.AUTH_USER_MODEL), + ), + ] diff -r 7f3fdcba7902 -r 6c879e963a93 src/iconolab/migrations/0026_auto_20170524_1107.py --- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/src/iconolab/migrations/0026_auto_20170524_1107.py Wed May 24 16:45:53 2017 +0200 @@ -0,0 +1,104 @@ +# -*- coding: utf-8 -*- +# Generated by Django 1.10.5 on 2017-05-24 11:07 +from __future__ import unicode_literals + +from django.db import migrations + +AWAITING = 0 +ACCEPTED = 1 +REJECTED = 2 +STUDIED = 3 + + +# This is a bit of code repetition because you can not access object methods +def set_tags_stats(obj, apps): + Tag = apps.get_model('iconolab', 'Tag') + obj.tag_count = Tag.objects.filter( + tagginginfo__revision=obj.annotation.current_revision).distinct().count() + obj.relevant_tags_count = relevant_tags_count_calc(obj, apps) + obj.accurate_tags_count = accurate_tags_count_calc(obj, apps) + +def relevant_tags_count_calc(obj, apps): + TaggingInfo = apps.get_model('iconolab', 'TaggingInfo') + return TaggingInfo.objects.filter(revision=obj.annotation.current_revision, relevancy__gte=3).distinct().count() + +def accurate_tags_count_calc(obj, apps): + TaggingInfo = apps.get_model('iconolab', 'TaggingInfo') + return TaggingInfo.objects.filter(revision=obj.annotation.current_revision, accuracy__gte=3).distinct().count() + +def contributors(obj, apps): + User = apps.get_model('auth', 'User') + user_ids_list = obj.annotation.revisions.filter( + state__in=[ACCEPTED, STUDIED] + ).values_list("author__id", flat=True) + return User.objects.filter(id__in=user_ids_list).distinct() + + +def update_stats(obj, apps): + # views_count - Can't do much about views count + # submitted_revisions_count + annotation_revisions = obj.annotation.revisions + obj.submitted_revisions_count = annotation_revisions.count() + # aawaiting_revisions_count + obj.awaiting_revisions_count = annotation_revisions.filter( + state=AWAITING).count() + # accepted_revisions_count + obj.accepted_revisions_count = annotation_revisions.filter(state=ACCEPTED).count( + ) + annotation_revisions.filter(state=STUDIED).count() + # comment_count + Comment = apps.get_model('django_comments', 'Comment') + obj.comments_count = Comment.objects.filter( + object_pk=obj.annotation.pk, + ).count() + # contributors + contrib_list = contributors(obj,apps) + obj.contributors.set(contrib_list) + obj.contributors_count = len(contrib_list) + # tag_count + + IconolabComment = apps.get_model('iconolab', 'IconolabComment') + annotation_comments_with_metacategories = IconolabComment.objects.filter( + content_type__app_label="iconolab", + content_type__model="annotation", + object_pk=obj.annotation.id, + metacategories__collection=obj.annotation.image.item.collection + ) + MetaCategoriesCountInfo = apps.get_model('iconolab', 'MetaCategoriesCountInfo') + m2m_objects = MetaCategoriesCountInfo.objects.filter( + annotation_stats_obj=obj) + for obj1 in m2m_objects.all(): + obj1.count = 0 + obj1.save() + for comment in annotation_comments_with_metacategories.all(): + for metacategory in comment.metacategories.all(): + if metacategory not in obj.metacategories.all(): + MetaCategoriesCountInfo.objects.create( + annotation_stats_obj=obj, metacategory=metacategory, count=1) + else: + m2m_object = MetaCategoriesCountInfo.objects.filter( + annotation_stats_obj=obj, metacategory=metacategory).first() + m2m_object.count += 1 + m2m_object.save() + set_tags_stats(obj, apps) + obj.save() + + + + +def update_annotation_stats_for_accurate_relevant_tag_count(apps, schema_editor): + AnnotationStats = apps.get_model('iconolab', 'AnnotationStats') + for ann_stats_obj in AnnotationStats.objects.all(): + update_stats(ann_stats_obj, apps) + + +class Migration(migrations.Migration): + + dependencies = [ + ('iconolab', '0025_annotationstats_contributors'), + ('auth', '0008_alter_user_username_max_length'), + ('django_comments', '0002_update_user_email_field_length') + ] + + operations = [ + migrations.RunPython(update_annotation_stats_for_accurate_relevant_tag_count), + ] diff -r 7f3fdcba7902 -r 6c879e963a93 src/iconolab/models.py --- a/src/iconolab/models.py Tue May 23 19:10:45 2017 +0200 +++ b/src/iconolab/models.py Wed May 24 16:45:53 2017 +0200 @@ -81,11 +81,11 @@ original_id = models.CharField(max_length=256, null=True, blank=True) display_image = models.ImageField(blank=True, null=True, upload_to='uploads/') - @property + @cached_property def items(self): return Item.objects.filter(folders=self) - @property + @cached_property def items_count(self): return self.items.count() @@ -93,11 +93,12 @@ def image(self): if self.display_image: return self.display_image - first_item = self.items.first() - if not first_item: - return None - images = Image.objects.filter(item=first_item) - first_image = images.first() + first_image = Image.objects.filter(item__folders=self).order_by('item__id', '-name').first() + # first_item = self.items.first() + # if not first_item: + # return None + # images = Image.objects.filter(item=first_item) + # first_image = images.first() return first_image.media if first_image else None def __str__(self): @@ -115,9 +116,11 @@ def __str__(self): return str(self.item_guid) + ":from:" + self.collection.name - @property + @cached_property def images_sorted_by_name(self): - return self.images.order_by("-name").all() + res = list(self.images.all()) + res.sort(key=lambda img: img.name, reverse=True) + return res class ItemMetadata(models.Model): @@ -566,9 +569,12 @@ accepted_revisions_count = models.IntegerField( blank=True, null=True, default=1) contributors_count = models.IntegerField(blank=True, null=True, default=1) + contributors = models.ManyToManyField(User, related_name='+') views_count = models.IntegerField(blank=True, null=True, default=0) comments_count = models.IntegerField(blank=True, null=True, default=0) tag_count = models.IntegerField(blank=True, null=True, default=0) + relevant_tags_count = models.IntegerField(blank=True, null=True, default=0) + accurate_tags_count = models.IntegerField(blank=True, null=True, default=0) metacategories = models.ManyToManyField( 'MetaCategory', through='MetaCategoriesCountInfo', @@ -578,14 +584,14 @@ def __str__(self): return "stats:for:" + str(self.annotation.annotation_guid) - @property - def contributors(self): + @cached_property + def contributors_list(self): user_ids_list = self.annotation.revisions.filter( state__in=[AnnotationRevision.ACCEPTED, AnnotationRevision.STUDIED] ).values_list("author__id", flat=True) return User.objects.filter(id__in=user_ids_list).distinct() - @property + @cached_property def commenters(self): user_ids_list = IconolabComment.objects.filter( content_type__app_label="iconolab", @@ -597,13 +603,13 @@ def set_tags_stats(self): self.tag_count = Tag.objects.filter( tagginginfo__revision=self.annotation.current_revision).distinct().count() + self.relevant_tags_count = self.relevant_tags_count_calc() + self.accurate_tags_count = self.accurate_tags_count_calc() - @property - def relevant_tags_count(self, score=settings.RELEVANT_TAGS_MIN_SCORE): + def relevant_tags_count_calc(self, score=settings.RELEVANT_TAGS_MIN_SCORE): return TaggingInfo.objects.filter(revision=self.annotation.current_revision, relevancy__gte=score).distinct().count() - @property - def accurate_tags_count(self, score=settings.ACCURATE_TAGS_MIN_SCORE): + def accurate_tags_count_calc(self, score=settings.ACCURATE_TAGS_MIN_SCORE): return TaggingInfo.objects.filter(revision=self.annotation.current_revision, accuracy__gte=score).distinct().count() @transaction.atomic @@ -622,8 +628,10 @@ self.comments_count = XtdComment.objects.for_app_models("iconolab.annotation").filter( object_pk=self.annotation.pk, ).count() - # contributors_count - self.contributors_count = len(self.contributors) + # contributors + contrib_list = self.contributors_list + self.contributors.set(contrib_list) + self.contributors_count = len(contrib_list) # tag_count annotation_comments_with_metacategories = IconolabComment.objects.filter( @@ -657,7 +665,7 @@ Metacategories are linked to comments, themselve linked to an annotation """ annotation_stats_obj = models.ForeignKey( - 'AnnotationStats', on_delete=models.CASCADE) + 'AnnotationStats', on_delete=models.CASCADE, related_name='metacategoriescountinfos') metacategory = models.ForeignKey('MetaCategory', on_delete=models.CASCADE) count = models.IntegerField(default=1, blank=False, null=False) diff -r 7f3fdcba7902 -r 6c879e963a93 src/iconolab/templates/iconolab/collection_home.html --- a/src/iconolab/templates/iconolab/collection_home.html Tue May 23 19:10:45 2017 +0200 +++ b/src/iconolab/templates/iconolab/collection_home.html Wed May 24 16:45:53 2017 +0200 @@ -23,8 +23,10 @@

{{ collection.description | safe }}

- {% if collection.folders.exists %} -

{{ collection.folders.count }} dossier{% if collection.folders.count > 1 %}s{% endif %}

+ {% with folders_count=collection.folders.count %} + {% if folders_count > 0 %} + +

{{ folders_count }} dossier{% if folders_count > 1 %}s{% endif %}

{% endif %} + {% endwith %}

{{ collection.verbose_name }} Fonds Iconolab

@@ -84,7 +89,7 @@

{{ annotation.current_revision.title }}

- {% for contributor in annotation.stats.contributors %} + {% for contributor in annotation.stats.contributors.all %} {{ contributor }} {% endfor %}

diff -r 7f3fdcba7902 -r 6c879e963a93 src/iconolab/templates/iconolab/home.html --- a/src/iconolab/templates/iconolab/home.html Tue May 23 19:10:45 2017 +0200 +++ b/src/iconolab/templates/iconolab/home.html Wed May 24 16:45:53 2017 +0200 @@ -76,7 +76,7 @@ {{ annotation.current_revision.created|naturaltime }}

- {% for contributor in annotation.stats.contributors %} + {% for contributor in annotation.stats.contributors.all %} {{ contributor.username }} {% endfor %}

diff -r 7f3fdcba7902 -r 6c879e963a93 src/iconolab/templates/partials/collection_home_pagination_links.html --- a/src/iconolab/templates/partials/collection_home_pagination_links.html Tue May 23 19:10:45 2017 +0200 +++ b/src/iconolab/templates/partials/collection_home_pagination_links.html Wed May 24 16:45:53 2017 +0200 @@ -8,19 +8,20 @@ {% endfor %} --> +{% with pagination_data_list=pagination_data.list%} -{% if pagination_data.list.has_previous or pagination_data.list.has_next %} +{% if pagination_data_list.has_previous or pagination_data_list.has_next %} {% endif %} + +{% endwith %} diff -r 7f3fdcba7902 -r 6c879e963a93 src/iconolab/templates/partials/item_images_preview.html --- a/src/iconolab/templates/partials/item_images_preview.html Tue May 23 19:10:45 2017 +0200 +++ b/src/iconolab/templates/partials/item_images_preview.html Wed May 24 16:45:53 2017 +0200 @@ -3,7 +3,8 @@
- {% with item.images_sorted_by_name.first as first_image %} + {% with item.images_sorted_by_name as images_list %} + {% with images_list.0 as first_image %}
{% thumbnail first_image.media "250x250" crop=False as im %} @@ -28,9 +29,9 @@ {% endif %}
- {% if item.images.count > 1 %} + {% if images_list|length > 1 %}
- {% for image in item.images_sorted_by_name %} + {% for image in images_list %} {% if image != first_image %} {% thumbnail image.media "100x100" crop=False as im %} @@ -58,5 +59,6 @@

{% endwith %} + {% endwith %}
diff -r 7f3fdcba7902 -r 6c879e963a93 src/iconolab/views/objects.py --- a/src/iconolab/views/objects.py Tue May 23 19:10:45 2017 +0200 +++ b/src/iconolab/views/objects.py Wed May 24 16:45:53 2017 +0200 @@ -1,23 +1,28 @@ -from django.shortcuts import HttpResponse, get_object_or_404, render, redirect -from django.http import Http404 -from django.db.models import Count +import logging + +from django.conf import settings +from django.contrib import messages from django.contrib.auth.decorators import login_required from django.contrib.auth.models import User -from django.contrib import messages -from django.views.generic import View, DetailView, RedirectView, TemplateView -from django.views.generic.base import ContextMixin -from django.core.paginator import Paginator, EmptyPage, PageNotAnInteger -from django.core.urlresolvers import reverse -from django.core.exceptions import ObjectDoesNotExist from django.contrib.contenttypes.models import ContentType from django.contrib.sites.models import Site -from django.conf import settings +from django.core.exceptions import ObjectDoesNotExist +from django.core.paginator import EmptyPage, PageNotAnInteger, Paginator +from django.core.urlresolvers import reverse +from django.db.models import Count, Prefetch +from django.http import Http404 +from django.shortcuts import HttpResponse, get_object_or_404, redirect, render +from django.views.generic import DetailView, RedirectView, TemplateView, View +from django.views.generic.base import ContextMixin from notifications.models import Notification -from iconolab.models import Annotation, AnnotationRevision, Collection, Folder, Item, Image, IconolabComment, MetaCategory, MetaCategoryInfo, BookmarkCategory, Bookmark, Tag, TaggingInfo + from iconolab.forms.annotations import AnnotationRevisionForm from iconolab.forms.bookmarks import BookmarkForm +from iconolab.models import (Annotation, AnnotationRevision, Bookmark, + BookmarkCategory, Collection, Folder, + IconolabComment, Image, Item, MetaCategory, + MetaCategoryInfo, Tag, TaggingInfo) from iconolab.serializers import AnnotationRevisionSerializer -import logging logger = logging.getLogger(__name__) @@ -43,14 +48,11 @@ # Best contributors count_contributions = Annotation.objects.all()\ .values('author').annotate(contributions=Count('author')).order_by('-contributions')[:10] - best_contributors = [] - for count_contribution in count_contributions: - author = User.objects.get(id=count_contribution['author']) - best_contributors.append({ - 'author': author, - 'contributions': count_contribution['contributions'] - }) - context['best_contributors'] = best_contributors + best_authors = { u.id: u for u in User.objects.filter(id__in=[cc['author'] for cc in count_contributions])} + context['best_contributors'] = [ + {'author': best_authors[cc['author']], 'contributions': cc['contributions']} + for cc in count_contributions + ] # Most accurate tags (tags with accuracy >= 4) # SELECT ti.tag_id, ar.title, COUNT(DISTINCT(a.id)) AS cnt @@ -61,21 +63,17 @@ # GROUP BY ti.tag_id # ORDER BY cnt desc rows = TaggingInfo.objects\ - .prefetch_related('revision', 'revision__annotation')\ .filter(accuracy__gte=4)\ .values('tag')\ .annotate(annotation_count=Count('revision__annotation', distinct=True))\ .order_by('-annotation_count')\ .all()[:10] - most_accurate_tags = [] - for row in rows: - tag = Tag.objects.get(id=row['tag']) - most_accurate_tags.append({ - 'tag': tag, - 'annotation_count': row['annotation_count'] - }) - context['most_accurate_tags'] = most_accurate_tags + best_tags = {t.id: t for t in Tag.objects.filter(id__in=[r['tag'] for r in rows])} + context['most_accurate_tags'] = [ + {'tag': best_tags[r['tag']], 'annotation_count': r['annotation_count']} + for r in rows + ] context['contact'] = settings.CONTACT_EMAIL context['homepage'] = True @@ -103,7 +101,14 @@ objects_tuple = () if 'collection_name' in kwargs.keys(): try: - objects_tuple += (Collection.objects.prefetch_related('items', 'items__images').get(name=kwargs.get('collection_name')),) + objects_tuple += (Collection.objects.prefetch_related( + 'items', + 'items__images', + Prefetch( + 'folders', + Folder.objects.annotate(items_nb=Count('item')).order_by('name') + ) + ).get(name=kwargs.get('collection_name')),) except (ValueError, Collection.DoesNotExist): return False, RedirectView.as_view(url=reverse('404error')) if 'item_guid' in kwargs.keys(): @@ -261,7 +266,7 @@ adjacent_pages_count = 2 # Paginated objects list - items_list = collection.items.order_by("metadatas__inventory_number") + items_list = collection.items.order_by("metadatas__inventory_number").prefetch_related('images', 'images__stats') folder = request.GET.get('folder', None) @@ -293,9 +298,10 @@ ) # Paginated recent annotations list - recent_annotations = Annotation.objects.filter(image__item__collection__name=collection.name).prefetch_related( + recent_annotations = Annotation.objects.filter(image__item__collection__name=collection.name).select_related('image', 'author').prefetch_related( 'current_revision', - 'stats' + 'stats', + 'stats__contributors' ).order_by('-current_revision__created') context["recent_pagination_data"] = self.get_pagination_data( recent_annotations, @@ -311,9 +317,10 @@ ) # Paginated revised annotations list - revised_annotations = Annotation.objects.filter(image__item__collection__name=collection.name).prefetch_related( + revised_annotations = Annotation.objects.filter(image__item__collection__name=collection.name).select_related('image', 'author').prefetch_related( 'current_revision', - 'stats' + 'stats', + 'stats__contributors' ).annotate(revision_count=Count('revisions')).order_by('-revision_count') context["revised_pagination_data"] = self.get_pagination_data( revised_annotations, @@ -333,7 +340,18 @@ metacategory__collection__name=collection.name, metacategory__triggers_notifications=MetaCategory.CONTRIBUTORS ).order_by('comment__submit_date').values_list('comment__object_pk', flat=True))) - collection_annotations = Annotation.objects.filter(id__in=contrib_calls_annotations_ids).all() + collection_annotations = \ + Annotation.objects.filter(id__in=contrib_calls_annotations_ids)\ + .select_related('image', 'current_revision')\ + .prefetch_related( + 'stats', + 'stats__contributors', + Prefetch( + 'current_revision__tagginginfo_set', + queryset=TaggingInfo.objects.select_related('tag') + ) + )\ + .all() collection_ann_dict = dict([(str(annotation.id), annotation) for annotation in collection_annotations]) contributions_annotations = [collection_ann_dict[id] for id in contrib_calls_annotations_ids] context["contributions_pagination_data"] = self.get_pagination_data(