CODEWITHSUNDEEP
pythondjango
jason
jason

Reputation: 602

Django - How to exclude instances in queryset due to foreign key model relation

I am trying to filter out the instances in Quotation model if there are any item instances that are not within a certain list of items. The certain list of items are retrieved from the ProjectItem model. The model structure is below, I will explain more after the code.

models.py

class SalesProject(models.Model):
    sales_project_id = models.AutoField(primary_key=True)
    sales_project_name = models.CharField(max_length=100)

class ProjectItem(models.Model):
    project_item_id = models.AutoField(primary_key=True)
    project = models.ForeignKey('SalesProject', related_name='items', on_delete=models.CASCADE)
    item = models.ForeignKey('Item', on_delete=models.CASCADE)
    remarks = models.TextField(max_length=1000)

class Quotation(models.Model):
    quotation_id = models.AutoField(primary_key=True)
    salesProject = models.ForeignKey(
        'SalesProject', related_name='quotations', on_delete=models.CASCADE, null=True, blank=True)
    details = models.TextField(max_length=1000, blank=True, null=True)

class QuotationItem(models.Model):
    quotation_item_id = models.AutoField(primary_key=True)
    item = models.ForeignKey('Item', on_delete=models.CASCADE, null=True, blank=True)
    quotation = models.ForeignKey('Quotation', on_delete=models.CASCADE, related_name='items', null=True, blank=True)
    remarks = models.TextField(max_length=1000)

class Item(models.Model):
    item_id = models.AutoField(primary_key=True)
    item_code = models.CharField(max_length=500, null=True, blank=True)
    item_description = models.TextField(max_length=1000, null=True, blank=True)

Firstly, I will get a list of Item instances by querying the current SalesProject. (where project = a SalesProject instance)

items = ProjectItem.objects.filter(project=project).values_list('item', flat=True)

Basically the Item instances that are within this items list are the 'permitted items' of sorts. What I would want to return is all the Quotation instances that contains only Item instances within this list (or to exclude any Quotation instances with Item instances outside of this list). The relation of Quotation to Item is through a QuotationItem model. Is there any way to do so?

Thanks all for the help, do guide me along, and let me know if there is insufficient information.

Upvotes: 1

Views: 762

Answers (1)

willeM_ Van Onsem
willeM_ Van Onsem

Reputation: 476584

You can filter with:

Quotation.objects.filter(items__item__projectitem__project=project)

You can make use of .distinct() to ensure you return each Quotation object only once:

Quotation.objects.filter(items__item__projectitem__project=project).distinct()

if you want Quotations that only refer to the project, you can make use of .exclude(…), or perhaps more straightforward, count the Projects:

from django.db.models import Count, Q

Quotation.objects.annotate(
    nproject=Count('items__item__projectitem__project', distinct=True),
    nproject_project=Count(
        'items__item__projectitem__project',
        distinct=True,
        filter=Q(items__item__projectitem__project=project)
    ),
).filter(
    nproject_project=1,
    nproject=1
).distinct()

Note: Your QuotationItem model basically acts like a through model of a ManyToManyField [Django-doc], you might want to add a ManyToManyField with this model as through=… parameter [Django-doc]. This makes querying such relations with the Django ORM more convenient.

Upvotes: 1

Related Questions