Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
825 views
in Technique[技术] by (71.8m points)

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

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(),
    ),
)
See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

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.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...