Hi, I’ve spent several hours to find a solution but I couldn’t: Case … When doesn’t seem to help. I can’t use Window because I have to do a calculation over rows not included in the queryset. I don’t see another solution than using raw SQL (or so) or fixing an (assumed) Django bug.
Does somebody know a solution? (I’m directly asking because it’s recommended in the docs to ask in this group: https://docs.djangoproject.com/en/2.1/topics/db/sql/#performing-raw-sql-queries) Anyway, thank you to every developer of the Django ORM with these powerful features you find in Django 2.0!! Daniel > Am 06.12.2018 um 16:31 schrieb Daniel Gilge <d.gi...@gmail.com>: > > Hi everyone, > > @Matthew and @Simon Thanks for your answers! > >> I assume you are trying to work >> around the cross join of multiple table annotations? > > This is correct. > >> There's tickets tracking adding subquery support to aggregate functions but >> using subqueries doesn't seem to be necessary here? > > Does this mean I can’t use subqueries in aggregate right now? (Doesn’t seem > to be the case because when I replace the filter in my code below with the > commented filter, the query produces expected results so far.) I had a look > at following ticket but it seems to be another case: > > Add support for aggregation through subqueries > https://code.djangoproject.com/ticket/28296 > <https://code.djangoproject.com/ticket/28296> > >> You may want to consider conditional expressions (Case … When) to achieve >> your goal. >> https://docs.djangoproject.com/en/2.1/ref/models/conditional-expressions/ >> <https://docs.djangoproject.com/en/2.1/ref/models/conditional-expressions/> > These functions attracted my attention when I was looking for a solution for > the described exception. I want to try to adjust my query and use them. (But > I thought that what I found is a bug. Therefore, I decided to write this > e-mail.) I will see if conditional expressions help. > > I actually didn’t want to bother you with the current code of my complete > query but I should probably send it to you to understand why I use subqueries > and filters that way. However, I’m not experienced with votes and such > complex queries and there might be a better solution. But I didn’t want to > ask you to take this work. > > This subquery is also used in other queries. Therefore, I placed it in a > function: > > def get_vote_subquery(role, user=None): > filter_fields = {'role': role} > if user: > filter_fields.update({'user': user}) > subquery = Subquery( > models.Vote.objects > .filter(segment=OuterRef('pk')) > .values('segment_id') > .annotate(sum=Sum('value', filter=Q(**filter_fields))) > .values('sum')[:1] > ) > return subquery > > The following code is slightly simplified. I didn’t manage to get the > necessary data with one query so far. (I’ll use Redis in the future to get > the votes. Therefore, it doesn’t have to be the most effective query for now). > > @property > def table_of_contents(self): > if self._table_of_contents: > return self._table_of_contents > > # Objects > headings = list( > models.Segment.objects > .filter(tag__in=IMPORTANT_HEADINGS) > .order_by('position') > .values( > 'pk', > 'position', > 'content', > ) > ) > > # Translation state (aggregation because annotation not possible) > aggregates = {} > required = REQUIRED_APPROVALS[self.language] > for i, h in enumerate(headings, start=1): > filters = {'position__gte': h['position']} > try: > filters['position__lt'] = headings[i]['position'] > except IndexError: > # The last item > pass > aggregates['translation_done_{}'.format(i)] = Count( > 'progress', # this is a field of the model > # filter=Q(**filters), > filter=Q(**filters) & ( > Q(progress__gte=TRANSLATION_DONE) > | Q(reviewers_vote__gte=1) > ), > ) > aggregates['review_done_{}'.format(i)] = Count( > 'reviewers_vote', > # filter=Q(**filters), > filter=Q(reviewers_vote__gte=required['reviewer'], **filters), > ) > aggregates['trustee_done_{}'.format(i)] = Count( > 'trustees_vote', > # filter=Q(**filters), > filter=Q(trustees_vote__gte=required['trustee'], **filters), > ) > aggregates = ( > models.Segment.objects > .annotate( > reviewers_vote=get_vote_subquery('reviewer'), > trustees_vote=get_vote_subquery('trustee'), > ) > .aggregate(**aggregates) > ) > > # Assign some values > for i, h in enumerate(headings, start=1): > try: > h['segments'] = headings[i]['position'] - h['position'] > except IndexError: > # The last item > h['segments'] = self.segments_count - h['position'] + 1 > h['translation_done'] = > aggregates['translation_done_{}'.format(i)] > h['review_done'] = aggregates['review_done_{}'.format(i)] > h['trustee_done'] = aggregates['trustee_done_{}'.format(i)] > self._table_of_contents = headings > return self._table_of_contents > > > Cheers, > > Daniel > >> Am 06.12.2018 um 06:37 schrieb Simon Charette <charett...@gmail.com >> <mailto:charett...@gmail.com>>: >> >> Hello Daniel, >> >> It's hard to tell was causes the exception without digging a bit more but >> based on your mention of multiple sums I assume you are trying to work >> around the cross join of multiple table annotations? >> >> I understand you simplified your query but from what I can see right now >> it can be expressed as >> >> commens_count = Comment.objects.annotate( >> votes_count=Count('votes', filter=votes_filter), >> ).filter( >> votes_count__gte=4 >> ).count() >> >> That should result in >> >> SELECT COUNT(*) FROM ( >> SELECT 1 >> FROM comment >> LEFT OUTER JOIN vote ON (vote.comment_id = comment.id >> <http://comment.id/>) >> GROUP BY comment.id <http://comment.id/> >> HAVING COUNT(*) FILTER (WHERE ...) > 4 >> ) >> >> There's tickets tracking adding subquery support to aggregate functions but >> using subqueries doesn't seem to be necessary here? >> >> Cheers, >> Simon >> >> Le mercredi 5 décembre 2018 19:16:30 UTC-5, Daniel Gilge a écrit : >> Hi, >> >> I think I've found a bug. But I'm not sure and I never opened a ticket for >> Django before. So, I first wanted to ask here. >> >> I'm using Django 2.0 and this doesn't work: >> >> subquery = Subquery( >> Vote.objects >> .filter(comment=OuterRef('pk')) >> .values('value')[:1] >> ) >> >> Comment.objects.annotate(vote=subquery).aggregate( >> count=Count('vote', filter=Q(vote__gte=4)), >> ) >> >> It results in a quite useless AssertionError: >> >> django/db/models/expressions.py >> >> 168 >> 169 def set_source_expressions(self, exprs): >> --> 170 assert len(exprs) == 0 >> 171 >> 172 def _parse_expressions(self, *expressions): >> >> AssertionError: >> >> >> Vars: >> exprs >> [Ref(__col8, Col(U0, myapp.Vote.comment))] >> self >> <django.db.models.expressions.Subquery object at 0x1080077b8> >> >> >> It probably doesn't make sense because I simplified it. Why I'm using >> subqueries is that I have several sums involved in the query: >> >> subquery = Subquery( >> Vote.objects >> .filter(comment=OuterRef('pk')) >> .values('comment_id') >> .annotate(sum=Sum('value', filter=Q(**filter_fields))) >> .values('sum')[:1] >> ) >> >> However, what I had to remove is a filter statement and then it works: >> >> Comment.objects.annotate(vote=subquery).aggregate( >> count=Count('vote'), >> ) >> >> Any advice appreciated! >> >> -- >> You received this message because you are subscribed to a topic in the >> Google Groups "Django users" group. >> To unsubscribe from this topic, visit >> https://groups.google.com/d/topic/django-users/JfZguPo-G_g/unsubscribe >> <https://groups.google.com/d/topic/django-users/JfZguPo-G_g/unsubscribe>. >> To unsubscribe from this group and all its topics, send an email to >> django-users+unsubscr...@googlegroups.com >> <mailto:django-users+unsubscr...@googlegroups.com>. >> To post to this group, send email to django-users@googlegroups.com >> <mailto:django-users@googlegroups.com>. >> Visit this group at https://groups.google.com/group/django-users >> <https://groups.google.com/group/django-users>. >> To view this discussion on the web visit >> https://groups.google.com/d/msgid/django-users/44dcbf0e-e766-4ba9-a9e6-05ebf1a1709f%40googlegroups.com >> >> <https://groups.google.com/d/msgid/django-users/44dcbf0e-e766-4ba9-a9e6-05ebf1a1709f%40googlegroups.com?utm_medium=email&utm_source=footer>. >> For more options, visit https://groups.google.com/d/optout >> <https://groups.google.com/d/optout>. > -- You received this message because you are subscribed to the Google Groups "Django users" group. To unsubscribe from this group and stop receiving emails from it, send an email to django-users+unsubscr...@googlegroups.com. To post to this group, send email to django-users@googlegroups.com. Visit this group at https://groups.google.com/group/django-users. To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/2A73C328-8898-4720-894B-087F419C5CA8%40gmail.com. For more options, visit https://groups.google.com/d/optout.
