i'm using django and i want a query to get a list phone number (12 phones number) with condition Vote has type bad and list phone is most type vote is bad. Please help me how to query to get list phone with conditions that. Eg: phone 1 has 10 vote bad, phone 2 has 1 vote bad, phone 3 has 5 vote bad, phone 4 has 2 vote bad. list_phone_i_want_3_item = [phone 1, phone3, phone4] with [10 vote bad, 5 vote bad, 2 vote bad]

bellow is my models

   class Phone(models.Model):
        phone_number = models.TextField(max_length=15, verbose_name="phone", default="")
        view = models.IntegerField(default=1, verbose_name="view")
        create = models.DateTimeField(auto_now_add=True, verbose_name="create")
        status = models.BooleanField(default=True, verbose_name="status")
    
        def __str__(self) -> str:
            return self.phone_number

class Vote(models.Model):
    # type vote : 1 is Normal 
    # 2 is Spam
    # 3 is Bad
    type_vote = models.IntegerField(default=1, verbose_name="type vote")
    phone = models.ForeignKey(Phone,on_delete=models.CASCADE, related_name="phone")
    comment = models.TextField(verbose_name="comment")
    create = models.DateTimeField(auto_now_add=True, verbose_name="creat")
    status = models.BooleanField(default=True, verbose_name="status")

    def __str__(self) -> str:
        return self.comment

CodePudding user response：

You can list the twelve Phones with the most Bad votes with:

from django.db.models import Count

Phone.objects.alias(
    count_bad=Count('phone', filter=Q(phone__type_vote=3))
).order_by('-count_bad')[:12]

or if you only want to list Phones that have at least one bad Vote, then this is:

from django.db.models import Count

Phone.objects.filter(
    phone__type_vote=3
).alias(
    count_bad=Count('phone')
).order_by('-count_bad')[:12]

Note: The related_name=… parameter ^[Django-doc] is the name of the relation in reverse, so from the Phone model to the Vote model in this case. Therefore it (often) makes not much sense to name it the same as the forward relation. You thus might want to consider renaming the phone relation to votes.