CODEWITHSUNDEEP
pythondjangodjango-models
Yantra Logistics
Yantra Logistics

Reputation: 308

Query multiple foreign keys in Django

I have two models Kits and Products:

Models.py

class Kit(models.Model):
    kit_name = models.CharField(max_length=255, default=0)
    kit_info = models.CharField(max_length=255, default=0)
    components_per_kit = models.IntegerField(default=0)

    product1 = models.ForeignKey(Product,on_delete=models.CASCADE, related_name='kit_product1')
    product1_information = models.CharField(max_length=255, default=0)
    product1_quantity = models.IntegerField(default=0)
    product1_unitprice = models.IntegerField(default=0)

    product2 = models.ForeignKey(Product,on_delete=models.CASCADE, related_name='kit_product2')
    product2_information = models.CharField(max_length=255, default=0)
    product2_quantity = models.IntegerField(default=0)
    product2_unitprice = models.IntegerField(default=0)

    product3 = models.ForeignKey(Product,on_delete=models.CASCADE, related_name='kit_product3')
    product3_information = models.CharField(max_length=255, default=0)
    product3_quantity = models.IntegerField(default=0)
    product3_unitprice = models.IntegerField(default=0)

    product4 = models.ForeignKey(Product,on_delete=models.CASCADE, related_name='kit_product4')
    product4_information = models.CharField(max_length=255, default=0)
    product4_quantity = models.IntegerField(default=0)
    product4_unitprice = models.IntegerField(default=0)

    product5 = models.ForeignKey(Product,on_delete=models.CASCADE, related_name='kit_product5')
    product5_information = models.CharField(max_length=255, default=0)
    product5_quantity = models.IntegerField(default=0)
    product5_unitprice = models.IntegerField(default=0)

    kit_client = models.ForeignKey(Client, on_delete=models.CASCADE, related_name='kit_owner')

    def __str__ (self):
        return self.kit_name

class Product(models.Model):
    category = (('Metal', 'Metal'), ('Wood', 'Wood'), ('Plastic', 'Plastic'))
    product_id = models.IntegerField(default=0)
    product_name = models.CharField(max_length=255, default=0)
    product_category = models.CharField(max_length=255, default=0, choices=category)
    product_quantity = models.IntegerField(default=0)
    product_owner = models.ForeignKey(Employee, on_delete=models.CASCADE, related_name='product_owner')

and in my views.py I am querying the products like this:

def load_product (request):
    kit_id = request.GET.get('kit')
    product1 = Product.objects.filter(kit_product1=kit_id)
    product2 = Product.objects.filter(kit_product2=kit_id)
    product3 = Product.objects.filter(kit_product3=kit_id)
    product4 = Product.objects.filter(kit_product4=kit_id)
    product5 = Product.objects.filter(kit_product5=kit_id)

    products = list(chain(product1, product2, product3,product4,product5))

    print("products are", products)
    return render(request, 'packsapp/employee/kit_product_list_options.html', {'products': products})

Is there any other way I can access all the products in the kit because these queries return the following:

products are [<Product: 1111::Plastic Pallet>, <Product: 1122::Side wall>, <Product: 1133::Top Lid>, <Product: 1144::Insert>, <Product: 1155::Separator sheets>]

but I am not getting the quantity of products in a particular Kit.

How can I query all the products in a kit and their quantity?

Upvotes: 0

Views: 1530

Answers (1)

ruddra
ruddra

Reputation: 51938

You can try like this with Q:

from django.db.models import Q

products = Product.objects.filter(Q(kit_product1=kit_id)|Q(kit_product2=kit_id)|Q(kit_product3=kit_id))

If you want to add quantity of all products, then use .count()

print(products.count())

Also, you can annotate the count per kit product type(using conditional aggregation):

from django.db.models import Count, Case, IntegerField

qset = products.annotate(kit_product1_count=Count(Case(
    When(kit_product1=kit_id, then=1),
    output_field=IntegerField(),
))).annotate(kit_product2_count=Count(Case(
    When(kit_product2=kit_id, then=1),
    output_field=IntegerField(),
))).annotate(kit_product3_count=Count(Case(
    When(kit_product3=kit_id, then=1),
    output_field=IntegerField(),
)))

# Ways to get the kit count

qset.values('kit_product1_count', 'kit_product2_count', 'kit_product3_count')

for item in qset:
   print(item.kit_product1_count)
   print(item.kit_product2_count)
   ...

Upvotes: 1

Related Questions