Not only can you experience AnswerHub firsthand, but you can also ask and explore questions and answers about AnswerHub.
To get started, try typing a question in the search bar. If your question hasn't been asked, try posting a new question.
I want to create a filter of reputes for some period. A query is:
User.objects.exclude(date_joined__gt=end_period).filter( Q(reputes__date__gte=start_period) & Q(reputes__date__lte=end_period)).annotate(period_rep=Sum('reputes__value')).order_by('-is_active', '-period_rep').distinct()
After that i ran
python manage.py syncdb --all python manage.py migrate forum
But this solution is very slow. Ðow can I optimize the query?
Maybe there are other ways (without using reputes table)? General task is to get users who have been reputed from one date to another.
I've changed an
class ActionRepute(models.Model): action = models.ForeignKey(Action, related_name='reputes') date = models.DateTimeField(datetime.datetime.now, db_index=True) user = models.ForeignKey('User', db_index=True, related_name='reputes') value = models.IntegerField(db_index=True, default=0) by_canceled = models.BooleanField(default=False)
But the query has not working faster. Where the mistake?
I've tried this one. I've run
python manage.py sqlindexes forum
For getting indexes. After I've run
python manage.py dbshell
CREATE INDEX "forum_actionrepute_user_id" ON "forum_actionrepute" ("user_id");
into dbshell, but got an error
ERROR: relation "forum_actionrepute_user_id" already exists
I also tried
BEGIN; CREATE INDEX "forum_actionrepute_date" ON "forum_actionrepute" ("date"); COMMIT; BEGIN; CREATE INDEX "forum_actionrepute_value" ON "forum_actionrepute" ("value"); COMMIT;
How I understood the trouble in
I've tried to create an other query
User.objects.select_related('reputes__value', 'reputes').filter(reputes__in=ActionRepute.objects.filter(Q(date__gte=start_period) & Q(date__lte=end_period))).annotate(period_rep=Sum('reputes__value')).order_by('-is_active', '-period_rep').distinct()
But I had the same result. How can I optimizing the query (a calculation of sum of the reputes values)?
OK. I've gotten a generated query. In the query the Django platform has pasted GROUP BY by all columns in the query (there are about 15 columns). I think the problem in GROUP BY statement. What must I do for fix this trouble?
Answer by expert · Nov 10, 2011 at 09:10 AM
I don't know, is it normal or not but my solution is working
ar = ActionRepute.objects.filter(date__range=(start, end_period)).values('user__id').annotate(period_rep=Sum('value')).order_by('-period_rep').distinct()[a:b] users = User.objects.select_related('reputes__value', 'reputes').filter(id__in=[a['user__id'] for a in ar]).filter(reputes__date__range=(start, end_period)).annotate(period_rep=Sum('reputes__value')).order_by('-period_rep').distinct()
It's fast and doesn't depend on size of database. If I have mistakes, please show them to me.