Django: NotImplementedError: annotate() + distinct(fields) is not implemented

django, postgresql, python

Solution

Try this:

Question.objects.annotate(
    answer_amount=Count('answers'),
    is_user_agreed=F('answers__is_agreed'),
).order_by('id', '-answers__is_agreed').distinct('id')

If `question` has no `answers`, then `question.is_user_agreed` is `None`. If `question` has at least one `answer` with `answer.is_agreed=True`, then `question.is_agreed` is `True`. Otherwise, `is_user_agreed` is `False`.

Or this:

agreed_questons = Answer.objects.filter(
    is_agreed=True,
).values_list('question_id', flat=True).distinct()

Question.objects.annotate(
    answer_amount=Count('answers'),
    is_agreed=Case(
        When(id__in=agreed_questions, then=True),
        When(answers__isnull=True, then=None),
        default=False,
        output_field=NullBooleanField(),
    ),
)

`agreed_questons` is list of `id` of `questions`, that have at least one `answer` with `answer.is_agreed=True`.

Problem

There are 2 simple models: ``` class Question(TimeStampedModel): text = models.CharField(max_length=40) class Answer(TimeStampedModel): question = models.ForeignKey(Question, related_name='answers') is_agreed = models.BooleanField() author = models.ForeingKey(User, related_name='answers') ``` And there is my problem: ``` In [18]: Question.objects.count() Out[18]: 3 ``` I need to annotate queryset with 'is_user_agreed' and 'answers_amount' fields: ``` In [18]: user = User.objects.first() In [19]: qs = Question.objects.annotate( ...: is_user_agreed=Case( ...: When(answers__in=user.answers.filter(is_agreed=True), then=Value(True)), ...: When(answers__in=user.answers.filter(is_agreed=False), then=Value(False)), ...: default=Value(None), ...: output_field=NullBooleanField(), ...: ), ...: ).annotate(answers_amount=Count('answers')) ...: qs.count() Out[19]: 4 ``` ^ here count is 4, but I have only 3 questions in db :( So, I've tried with `distinct()` ``` In [20]: qs.distinct().count() Out[20]: 4 # but distinct doesn't work In [21]: qs.distinct('id').count() ``` And after last line of code I've got this exception: ``` NotImplementedError: annotate() + distinct(fields) is not implemented. ``` I've also tried to use this trick `annotate(Count('id')).filter(id__count__gt=1)` But in this case I'm losing all duplicate rows, and qs.count() is 2. UPDATE: The problem is duplicated rows in queryset. SOLUTION: (Extended variant of Vladimir's second approach) ``` user = User.objects.first() user_agreed_questions = user.answers.filter( is_agreed=True).values_list('question_id', flat=True) user_not_agreed_questions = user.answers.filter( is_agreed=False).values_list('question_id', flat=True) Question.objects.annotate( answer_amount=Count('answers'), is_user_agreed=Case( When(id__in=user_agreed_questions, then=True), When(id__in=user_not_agreed_questions, then=False), default=None, output_field=NullBooleanField(), ), ) ```

Original source

Related problems