Tickets 104, 107: User_stats view optimizations

4 files changed, 192 insertions, 221 deletions

diff --git a/askbot/models/post.py b/askbot/models/post.py
index b71d3bab..b3e6770e 100644
--- a/askbot/models/post.py
+++ b/askbot/models/post.py
@@ -5,14 +5,25 @@ from askbot.utils import markup
 from askbot.utils.html import sanitize_html
 from askbot.models import content
 
+class PostManager(models.Manager):
+    def get_questions(self):
+        return self.filter(post_type='question')
+
+    def get_answers(self):
+        return self.filter(post_type='answer')
+
 
 class Post(content.Content):
     post_type = models.CharField(max_length=255)
     parent = models.ForeignKey('Post', blank=True, null=True)
+
     self_answer = models.ForeignKey('Answer', blank=True, null=True)
     self_question = models.ForeignKey('Question', blank=True, null=True)
+
     thread = models.ForeignKey('Thread')
 
+    objects = PostManager()
+
     class Meta:
         app_label = 'askbot'
         db_table = 'askbot_post'
@@ -24,6 +35,12 @@ class Post(content.Content):
         real_post = self.self_answer or self.self_question
         real_post.delete(*args, **kwargs)
 
+    def is_answer_accepted(self):
+        if not self.is_answer():
+            raise NotImplementedError
+        return self.thread.accepted_answer_id and (self.thread.accepted_answer_id == self.self_answer_id)
+
+
 for field in Post._meta.fields:
     if isinstance(field, models.ForeignKey):
         # HACK: Patch all foreign keys to not cascade when deleted
diff --git a/askbot/skins/default/templates/user_profile/user_stats.html b/askbot/skins/default/templates/user_profile/user_stats.html
index 46d5a40d..f850c6ca 100644
--- a/askbot/skins/default/templates/user_profile/user_stats.html
+++ b/askbot/skins/default/templates/user_profile/user_stats.html
@@ -4,135 +4,137 @@
 {% block profilesection %}
     {% trans %}overview{% endtrans %}
 {% endblock %}
-    {% block usercontent %}
-        {% include "user_profile/user_info.html" %}
-        <a name="questions"></a>
-        {% spaceless %}
-            <h2>{% trans counter=question_count %}<span class="count">{{counter}}</span> Question{% pluralize %}<span class="count">{{counter}}</span> Questions{% endtrans %}</h2>
-        {% endspaceless %}
+{% block usercontent %}
+    {% include "user_profile/user_info.html" %}
+    <a name="questions"></a>
+    {% spaceless %}
+        <h2>{% trans counter=question_count %}<span class="count">{{counter}}</span> Question{% pluralize %}<span class="count">{{counter}}</span> Questions{% endtrans %}</h2>
+    {% endspaceless %}
     {% include "user_profile/users_questions.html" %}
-        <a name="answers"></a>
-        {% spaceless %}
-            <h2 style="clear:both;">{% trans counter=answered_questions|length %}<span class="count">{{counter}}</span> Answer{% pluralize %}<span class="count">{{counter}}</span> Answers{% endtrans %}</h2>
-        {% endspaceless %}
-        <div class="user-stats-table">
-            {% for answered_question in answered_questions %}
-            <div class="answer-summary">
-                <a title="{{answered_question.summary|collapse}}" 
-                    href="{% url question answered_question.id %}{{answered_question.title|slugify}}#{{answered_question.answer_id}}">
-                    <span class="answer-votes {% if answered_question.answer_accepted %}answered-accepted{% endif %}"
-                          title="{% trans answer_score=answered_question.answer_score %}the answer has been voted for {{ answer_score }} times{% endtrans %} {% if answered_question.answer_accepted %}{% trans %}this answer has been selected as correct{% endtrans %}{%endif%}">
-                        {{ answered_question.answer_score }}
-                    </span>
-                </a>
-                <div class="answer-link">
-                    {% spaceless %}
-                    <a href="{% url question answered_question.id %}{{answered_question.title|slugify}}#{{answered_question.answer_id}}">{{answered_question.title}}</a> 
-                    {% endspaceless %}
-                    {% if answered_question.comment_count %}
-                    <span>
-                        {% trans comment_count=answered_question.comment_count %}({{comment_count}} comment){% pluralize %}the answer has been commented {{comment_count}} times{% endtrans %}
-                    </span> 
-                    {% endif %}
-                </div>
+    <a name="answers"></a>
+    {% spaceless %}
+        <h2 style="clear:both;"><span class="count">{{counter}}</span> {% trans counter=top_answer_count %}Answer{% pluralize %}Answers{% endtrans %}</h2>
+    {% endspaceless %}
+    <div class="user-stats-table">
+        {% for top_answer in top_answers %}
+        <div class="answer-summary">
+            <a title="{{ top_answer.summary|collapse }}"
+                href="{% url question top_answer.parent_id %}{{ top_answer.thread.title|slugify }}#{{ top_answer.self_answer_id }}">
+                <span class="answer-votes {% if top_answer.is_answer_accepted() %}answered-accepted{% endif %}"
+                      title="{% trans answer_score=top_answer.score %}the answer has been voted for {{ answer_score }} times{% endtrans %} {% if top_answer.is_answer_accepted() %}{% trans %}this answer has been selected as correct{% endtrans %}{%endif%}">
+                    {{ top_answer.score }}
+                </span>
+            </a>
+            <div class="answer-link">
+                {% spaceless %}
+                <a href="{% url question top_answer.parent_id %}{{ top_answer.thread.title|slugify }}#{{top_answer.self_answer_id}}">{{ top_answer.thread.title }}</a>
+                {% endspaceless %}
+                {% if top_answer.comment_count > 0 %}
+                <span>
+                    {% trans comment_count=top_answer.comment_count %}({{ comment_count }} comment){% pluralize %}the answer has been commented {{ comment_count }} times{% endtrans %}
+                </span>
+                {% endif %}
             </div>
-            {% endfor %}
         </div>
-        <br/>
-        <a name="votes"></a>
-        {% spaceless %}
-            <h2>{% trans cnt=total_votes %}<span class="count">{{cnt}}</span> Vote{% pluralize %}<span class="count">{{cnt}}</span> Votes {% endtrans %}</h2>
-        {% endspaceless %}
-        <div class="user-stats-table">
-            <table>
-                <tr>
-                    <td width="60">
-                        <img style="cursor: default;" src="{{"/images/vote-arrow-up-on.png"|media}}" alt="{% trans %}thumb up{% endtrans %}" />
-                            <span title="{% trans %}user has voted up this many times{% endtrans %}" class="vote-count">{{up_votes}}</span>
-                    </td>
-                    <td width="60">
-                        <img style="cursor: default;" src="{{"/images/vote-arrow-down-on.png"|media}}" alt="{% trans %}thumb down{% endtrans %}" />
-                            <span title="{% trans %}user voted down this many times{% endtrans %}" class="vote-count">{{down_votes}}</span>
+        {% endfor %}
+    </div>
+    <br/>
+    <a name="votes"></a>
+    {% spaceless %}
+        <h2>{% trans cnt=total_votes %}<span class="count">{{cnt}}</span> Vote{% pluralize %}<span class="count">{{cnt}}</span> Votes {% endtrans %}</h2>
+    {% endspaceless %}
+    <div class="user-stats-table">
+        <table>
+            <tr>
+                <td width="60">
+                    <img style="cursor: default;" src="{{"/images/vote-arrow-up-on.png"|media}}" alt="{% trans %}thumb up{% endtrans %}" />
+                        <span title="{% trans %}user has voted up this many times{% endtrans %}" class="vote-count">{{up_votes}}</span>
+                </td>
+                <td width="60">
+                    <img style="cursor: default;" src="{{"/images/vote-arrow-down-on.png"|media}}" alt="{% trans %}thumb down{% endtrans %}" />
+                        <span title="{% trans %}user voted down this many times{% endtrans %}" class="vote-count">{{down_votes}}</span>
 
-                    </td>
-                </tr>
-            </table>
-        </div>
-        <a name="tags"></a>
-        {% spaceless %}
-            <h2>{% trans counter=user_tags|length %}<span class="count">{{counter}}</span> Tag{% pluralize %}<span class="count">{{counter}}</span> Tags{% endtrans %}</h2>
-        {% endspaceless %}
-        <div class="user-stats-table">
-            <table class="tags">
-                <tr>
-                    <td valign="top">
-                        <ul id="ab-user-tags" class="tags">
-                        {% for tag in user_tags %}
-                            <li>
-                            {{ macros.tag_widget(
-                                tag.name,
-                                html_tag = 'div',
-                                url_params = 
-                                    "author=" ~ view_user.id ~
-                                    "&start_over=true",
-                                extra_content = 
-                                    '<span class="tag-number">&#215; ' ~
-                                    tag.user_tag_usage_count|intcomma ~
-                                    '</span>'
-                               )
-                            }}
-                            </li>
-                            {#
-                            {% if loop.index is divisibleby 10 %}
-                                </td>
-                                <td width="180"  valign="top">
-                            {% endif %}
-                            #}
-                        {% endfor %}
+                </td>
+            </tr>
+        </table>
+    </div>
+    <a name="tags"></a>
+    {% spaceless %}
+        <h2>{% trans counter=user_tags|length %}<span class="count">{{counter}}</span> Tag{% pluralize %}<span class="count">{{counter}}</span> Tags{% endtrans %}</h2>
+    {% endspaceless %}
+    <div class="user-stats-table">
+        <table class="tags">
+            <tr>
+                <td valign="top">
+                    <ul id="ab-user-tags" class="tags">
+                    {% for tag in user_tags %}
+                        <li>
+                        {{ macros.tag_widget(
+                            tag.name,
+                            html_tag = 'div',
+                            url_params =
+                                "author=" ~ view_user.id ~
+                                "&start_over=true",
+                            extra_content =
+                                '<span class="tag-number">&#215; ' ~
+                                tag.user_tag_usage_count|intcomma ~
+                                '</span>'
+                           )
+                        }}
+                        </li>
+                        {#
+                        {% if loop.index is divisibleby 10 %}
+                            </td>
+                            <td width="180"  valign="top">
+                        {% endif %}
+                        #}
+                    {% endfor %}
+                    </ul>
+                </td>
+            </tr>
+        </table>
+    </div>
+    <a name="badges"></a>
+    {% spaceless %}
+    <h2>{% trans counter=total_badges %}<span class="count">{{counter}}</span> Badge{% pluralize %}<span class="count">{{counter}}</span> Badges{% endtrans %}</h2>
+    {% endspaceless %}
+    <div class="user-stats-table badges">
+        <table>
+            <tr>
+                <td style="line-height:35px">
+                    {% for badge, badge_user_awards in badges %}
+                        <a
+                            href="{{badge.get_absolute_url()}}"
+                            title="{% trans description=badge.description %}{{description}}{% endtrans %}"
+                            class="medal"
+                        ><span class="{{ badge.css_class }}">&#9679;</span>&nbsp;{% trans name=badge.name %}{{name}}{% endtrans %}
+                        </a>&nbsp;
+                        <span class="tag-number">&#215;
+                            <span class="badge-context-toggle">{{ badge_user_awards|length|intcomma }}</span>
+                        </span>
+{# TODO: Uncomment&adjust after Django view can prepare a list of Post-s for each award
+                        <ul id="badge-context-{{ badge.id }}" class="badge-context-list" style="display:none">
+                            {% for award in badge_user_awards %}
+                                {% if award.content_type in (question_type, answer_type) %}
+                                <li>
+                                    <a
+                                        title="{{ award.content_object.get_snippet()|collapse }}"
+                                        href="{{ award.content_object.get_absolute_url() }}"
+                                    >{% if award.content_type == answer_type %}{% trans %}Answer to:{% endtrans %}{% endif %} {{ award.content_object.thread.title }}</a>
+                                </li>
+                                {% endif %}
+                            {% endfor %}
                         </ul>
-                    </td>
-                </tr>
-            </table>
-        </div>
-        <a name="badges"></a>
-        {% spaceless %}
-        <h2>{% trans counter=total_awards %}<span class="count">{{counter}}</span> Badge{% pluralize %}<span class="count">{{counter}}</span> Badges{% endtrans %}</h2>
-        {% endspaceless %}
-        <div class="user-stats-table badges">
-            <table>
-                <tr>
-                    <td style="line-height:35px">
-                        {% for badge in badges %}{# todo: translate badge name properly #}
-                            <a 
-                                href="{{badge.get_absolute_url()}}"
-                                title="{% trans description=badge.description %}{{description}}{% endtrans %}"
-                                class="medal"
-                            ><span class="{{ badge.css_class }}">&#9679;</span>&nbsp;{% trans name=badge.name %}{{name}}{% endtrans %}
-                            </a>&nbsp;
-                            <span class="tag-number">&#215;
-                                <span class="badge-context-toggle">{{ badge.award_badge.count()|intcomma }}</span>
-                            </span>
-                            <ul id="badge-context-{{ badge.id }}" class="badge-context-list" style="display:none">
-                                {% for award in badge.award_badge.filter(user = view_user) %}
-                                    {% if award.content_type in (question_type, answer_type) %}
-                                    <li>
-                                        <a 
-                                            title="{{ award.content_object.get_snippet()|collapse }}"
-                                            href="{{ award.content_object.get_absolute_url() }}"
-                                        >{% if award.content_type == answer_type %}{% trans %}Answer to:{% endtrans %}{% endif %} {{ award.content_object.get_origin_post().title }}</a>
-                                    </li>
-                                    {% endif %}
-                                {% endfor %}
-                            </ul>
-                            {% if loop.index is divisibleby 3 %}
-                                </td></tr>
-                                <tr><td style="line-height:35px">
-                            {% endif %}
-                        {% endfor %}
-                    </td>
-                </tr>
-            </table>
-        </div>
+#}
+                        {% if loop.index is divisibleby 3 %}
+                            </td></tr>
+                            <tr><td style="line-height:35px">
+                        {% endif %}
+                    {% endfor %}
+                </td>
+            </tr>
+        </table>
+    </div>
 {% endblock %}
 {% block endjs %}
     {{ super() }}
diff --git a/askbot/views/readers.py b/askbot/views/readers.py
index 30843e3a..b75eafe2 100644
--- a/askbot/views/readers.py
+++ b/askbot/views/readers.py
@@ -617,6 +617,7 @@ def get_comment(request):
 @ajax_only
 @get_only
 def get_question_body(request):
+    # TODO: Is this used anywhere?
     search_state = request.session.get('search_state', SearchState())
     view_log = request.session['view_log']
     (qs, meta_data, related_tags) = models.Question.objects.run_advanced_search(
@@ -634,6 +635,7 @@ def widget_questions(request):
     """Returns the first x questions based on certain tags.
     @returns template with those questions listed."""
     # make sure this is a GET request with the correct parameters.
+    # TODO: Is this used anywhere?
     if request.method != 'GET':
         raise Http404
     questions = models.Question.objects.all()
diff --git a/askbot/views/users.py b/askbot/views/users.py
index 7ba10b6a..6cb784bc 100644
--- a/askbot/views/users.py
+++ b/askbot/views/users.py
@@ -10,7 +10,9 @@ import calendar
 import functools
 import datetime
 import logging
-from django.db.models import Count
+import operator
+
+from django.db.models import Count, Sum
 from django.conf import settings as django_settings
 from django.contrib.auth.decorators import login_required
 from django.core.paginator import Paginator, EmptyPage, InvalidPage
@@ -276,114 +278,55 @@ def edit_user(request, id):
     return render_into_skin('user_profile/user_edit.html', data, request)
 
 def user_stats(request, user, context):
-
-    question_filter = {'author': user}
+    question_filter = {}
     if request.user != user:
         question_filter['is_anonymous'] = False
 
-    questions = models.Question.objects.filter(
-                                    **question_filter
-                                ).order_by(
-                                    '-score', '-thread__last_activity_at'
-                                ).select_related(
-                                    'thread',
-                                    'thread__last_activity_by__id',
-                                    'thread__last_activity_by__username',
-                                    'thread__last_activity_by__reputation',
-                                    'thread__last_activity_by__gold',
-                                    'thread__last_activity_by__silver',
-                                    'thread__last_activity_by__bronze'
-                                )[:100]
-
-    questions = list(questions)
+    questions = user.posts.get_questions().filter(**question_filter).\
+                    order_by('-score', '-thread__last_activity_at').\
+                    select_related('thread', 'thread__last_activity_by')[:100]
 
     #added this if to avoid another query if questions is less than 100
     if len(questions) < 100:
         question_count = len(questions)
     else:
-        question_count = models.Question.objects.filter(
-                                        **question_filter
-                                    ).order_by(
-                                        '-score', '-thread__last_activity_at'
-                                    ).select_related(
-                                        'thread__last_activity_by__id',
-                                        'thread__last_activity_by__username',
-                                        'thread__last_activity_by__reputation',
-                                        'thread__last_activity_by__gold',
-                                        'thread__last_activity_by__silver',
-                                        'thread__last_activity_by__bronze'
-                                    ).count()
-
-# Commented out, doesn't seem to be used
-#    threads = [quest.thread for quest in questions]
-#    favorited_myself = models.FavoriteQuestion.objects.filter(
-#                                    thread__in = threads,
-#                                    user = user
-#                                ).values_list('thread__id', flat=True)
+        question_count = user.posts.get_questions().filter(**question_filter).count()
 
     #this is meant for the questions answered by the user (or where answers were edited by him/her?)
-    answered_questions = models.Question.objects.extra(
-        select={
-            'vote_up_count' : 'answer.vote_up_count',
-            'vote_down_count' : 'answer.vote_down_count',
-            'answer_id' : 'answer.id',
-            'answer_accepted' : 'askbot_thread.accepted_answer_id',
-            'answer_score' : 'answer.score',
-            'comment_count' : 'answer.comment_count',
-            'title': 'askbot_thread.title',
-            },
-        tables=['question', 'answer', 'askbot_thread'],
-        where=['NOT answer.deleted AND NOT question.deleted AND answer.author_id=%s AND answer.question_id=question.id AND question.thread_id=askbot_thread.id'],
-        params=[user.id],
-        order_by=['-answer_score', '-answer_id'],
-        select_params=[user.id]
-    ).distinct().values('comment_count',
-                        'id',
-                        'answer_id',
-                        'title',
-                        'author_id',
-                        'answer_accepted',
-                        'answer_score',
-                        #'answer_count', Moved from Question to Thread and doesn't seem to be referenced anywhere !?
-                        'vote_up_count',
-                        'vote_down_count')[:100]
+    top_answers = user.posts.get_answers().filter(
+        deleted=False,
+        parent__deleted=False,
+    ).select_related('thread').order_by('-score', '-id')[:100]
 
+    top_answer_count = len(top_answers)
 
     up_votes = models.Vote.objects.get_up_vote_count_from_user(user)
     down_votes = models.Vote.objects.get_down_vote_count_from_user(user)
     votes_today = models.Vote.objects.get_votes_count_today_from_user(user)
     votes_total = askbot_settings.MAX_VOTES_PER_USER_PER_DAY
 
-    question_id_set = set()
-    #todo: there may be a better way to do these queries
-    question_id_set.update([q.id for q in questions])
-    question_id_set.update([q['id'] for q in answered_questions])
-    #user_tags = models.Tag.objects.filter(questions__id__in = question_id_set)
-    user_tag_ids = models.Question.objects.filter(id__in=question_id_set).values_list('thread__tags__id', flat=True)
-    user_tags = models.Tag.objects.filter(id__in=user_tag_ids)
-
-    badges = models.BadgeData.objects.filter(
-                            award_badge__user=user
-                        )
-    total_awards = badges.count()
-    badges = badges.order_by('-slug').distinct()
-
-    user_tags = user_tags.annotate(
-                            user_tag_usage_count=Count('name')
-                        ).order_by(
-                            '-user_tag_usage_count'
-                        )
+    thread_id_set = set()
+    thread_id_set.update([qq.thread_id for qq in questions])
+    thread_id_set.update([aa.thread_id for aa in top_answers])
+    user_tags = models.Tag.objects.filter(threads__id__in=thread_id_set).\
+                    annotate(user_tag_usage_count=Count('threads')).\
+                    order_by('-user_tag_usage_count')[:const.USER_VIEW_DATA_SIZE]
+    user_tags = list(user_tags) # DEBUG: evaluate
+
+    user_awards = models.Award.objects.filter(user=user).select_related('badge')
+    badges_dict = {}
+    for award in user_awards:
+        if award not in badges_dict:
+            badges_dict[award.badge] = [award]
+        else:
+            badges_dict[award.badge].append(award)
+    badges = list(badges_dict.items())
+    badges.sort(key=lambda badge_tuple: len(badge_tuple[1]), reverse=True)
+    # TODO: fetch award.content_object in one query, as a list of Post-s, and pass to template to avoid subsequent queries there
+    total_badges = len(badges)
 
-    if user.is_administrator():
-        user_status = _('Site Adminstrator')
-    elif user.is_moderator():
-        user_status = _('Forum Moderator')
-    elif user.is_suspended():
-        user_status = _('Suspended User')
-    elif user.is_blocked():
-        user_status = _('Blocked User')
-    else:
-        user_status = _('Registered User')
+#    badges = models.BadgeData.objects.filter(award_badge__user=user).annotate(user_awarded_times=Count('award_badge'))
+#    total_badges = len(badges)
 
     data = {
         'active_tab':'users',
@@ -397,19 +340,26 @@ def user_stats(request, user, context):
         'question_count': question_count,
         'question_type' : ContentType.objects.get_for_model(models.Question),
         'answer_type' : ContentType.objects.get_for_model(models.Answer),
-# Commented out, doesn't seem to be used
-#        'favorited_myself': favorited_myself,
-        'answered_questions' : answered_questions,
+
+        #'answered_questions' : answered_questions,
+        'top_answers': top_answers,
+        'top_answer_count': top_answer_count,
+
         'up_votes' : up_votes,
         'down_votes' : down_votes,
         'total_votes': up_votes + down_votes,
-        'votes_today_left': votes_total-votes_today,
+        'votes_today_left': votes_total - votes_today,
         'votes_total_per_day': votes_total,
-        'user_tags' : user_tags[:const.USER_VIEW_DATA_SIZE],
+
+        'user_tags' : user_tags,
+
         'badges': badges,
-        'total_awards' : total_awards,
+        'total_badges' : total_badges,
     }
     context.update(data)
+
+    len(models.Post.objects.filter(post_type='mikki')) # DEBUG: distinctive query
+
     return render_into_skin('user_profile/user_stats.html', context, request)
 
 def user_recent(request, user, context):