CODEWITHSUNDEEP
djangodjango-queryset
Diand
Diand

Reputation: 888

Django 2.2 ORM Exclude Not Working as expected

Im trying to get all customer list form Cust models that not having a record in Stat (ForeignKey), Django 2.2


class Cust(models.Model):
    name = models.CharField(max_length=50)
    active = models.BooleanField(default=True)


class Stat(models.Model):
    cust = models.ForeignKey(Cust, on_delete=models.PROTECT, null=True)
    date = models.DateField(null=True, blank=True)

im trying this but doestn work,

    month = datetime.now()
    month = month.strftime("%Y-%m")
    
    inner_qs = Stat.objects.filter(date__icontains=month)
    data = Cust.objects.exclude(id__in=inner_qs)
    
    print(inner_qs)
    print(data)

The query above returning:


<QuerySet [<Stat: Ruth>]>
<QuerySet [<Cust: Jhonny>, <Cust: Rony>, <Cust: Sinta>, <Cust: Ruth>]>

As you can see, i need the result [<Stat: Ruth>] excluded from the data queryset/list.

but what i expected is:

<QuerySet [<Stat: Ruth>]>
<QuerySet [<Cust: Jhonny>, <Cust: Rony>, <Cust: Sinta>>

Upvotes: 1

Views: 401

Answers (1)

ha-neul
ha-neul

Reputation: 3248

According to django doc 1 about __in, it states:

In a given iterable; often a list, tuple, or queryset. It’s not a common use case, but strings (being iterables) are accepted.

There are several ways to solve this problem.

replace

    inner_qs = Stat.objects.filter(date__icontains=month)
    data = Cust.objects.exclude(id__in=inner_qs)

1. with

    inner_qs = Stat.objects.filter(date__icontains=month)
    data = Cust.objects.exclude(id__in=[o.cust_id for o in inner_qs])

2. Another way is to replace with

    id_list =Stat.objects.filter(date__icontains=month)\
            .values_list('cust_id', flat=True)\ 
            # flat=True will return list rather 
            # than tuple in the ValueListQueryset

    data = Cust.objects.exclude(id__in=id_list)

Here, you first generate an exclude id_list then use it to exclude.

3. a revised way of #2 with distinct()

    id_list =Stat.objects.filter(date__icontains=month)\
            .values_list('cust_id', flat=True)\
            .distinct().order_by() 
            # distinct() is used to get unique 'cust_id's
            # distinct() does not work without order_by()

    data = Cust.objects.exclude(id__in=id_list)

Upvotes: 2

Related Questions