CODEWITHSUNDEEP
django
user4227817
user4227817

Reputation:

Django query set across foreign keys with count

I am in need of some assistance with a query. I have three models linked by foreign keys these are Suppliers <-> Restaurants <-> Ingredients. I need to get the names of the five suppliers who have the largest number of ingredients to restock. My models can be seen below, from my understanding I will need to firstly filter for just the ingredients that have a stock_status = "out of stock". I will then need to perform a count on them probably through annotation, taking this Count and ordering it. Finally I will take take the first five using on the .orderby()[5:]. I am unsure as to how to put this all together, and would appreciate any help available.

class Ingredients(models.Model):
    out_of_stock = "out_of_stock"
    partial = "partial"
    full = "full"
    STOCK_CHOICES = (
        (out_of_stock, 'Out of stock'),
        (partial, 'Partial'),
        (full, 'Full'),
    )
    stock = CharField(choices=STOCK_CHOICES, max_length=12)
    name = models.CharField(max_length=32, blank=False, null=False)
    restaurant_name = models.ForeignKey(Restaurant, on_delete=models.CASCADE)

class Restaurant(models.Model):
    r_name = models.CharField(max_length=32, blank=False, null=False)
    supplier_name = models.ForeignKey(Supplier, on_delete=models.CASCADE)

class Supplier(models.Model):
    name = models.CharField(max_length=32, blank=False, null=False)

Sorry if it is a little unclear, I am struggling to put together what is in my mind and what I am writing at the moment. If you need any clarification just drop a comment. Thanks in advance!

Upvotes: 1

Views: 261

Answers (1)

Nihal Sharma
Nihal Sharma

Reputation: 2437

You are going in the right direction. This will give you the list of suppliers grouped by the count of OOS ingredients.

ss = Supplier.objects.filter(restaurant__ingredients__stock="Out of stock").annotate(count=Count('restaurant__ingredients__stock')).order_by('-count')

The output is as:

<QuerySet [<Supplier: Supplier object (1)>, <Supplier: Supplier object (2)>]>

to get count

ss[0].count == 3

Now, if you want to pick 5 of them -

top_five = ss[:5]

Upvotes: 2

Related Questions