how to find top performing category in django without foreign key-CodePudding

models.py

class Line_items(models.Model):
  id = models.AutoField(primary_key=True)
  product = models.ForeignKey('Products' , on_delete=models.DO_NOTHING )

class Products(models.Model):
  id = models.AutoField(primary_key=True)
  name = models.CharField(max_length=400 , blank=True)

class Categories(models.Model):
  id = models.AutoField(primary_key=True)
  name = models.CharField(max_length=100 , blank=True)
  slug = models.CharField(max_length=200 , blank=True)

class Product_Categories(models.Model):
  id  = models.AutoField(primary_key=True)
  product_id  = models.ForeignKey(Products, on_delete=models.DO_NOTHING)
  category_id =  models.ForeignKey(Categories, on_delete=models.DO_NOTHING)

here are my models. where line_items contains number of orders done till now. in line_items we have connect product id with product table. but we don't have any connetion from product table to category table. ( category table contains every category and their id ). to connect product table with category table we have created new table 'product_categories' which connects each category with their respective product.

here what we want is top performing category. category which have highest number of products. thanks

CodePudding user response：

It makes sense to add a ManyToManyField to join the Products and Categories models using Product_Categories as the through table

class Products(models.Model):
    id = models.AutoField(primary_key=True)
    name = models.CharField(max_length=400 , blank=True)
    # Field added below
    categories = models.ManyToManyField('Categories', through='Product_Categories', related_name='products')

class Categories(models.Model):
    id = models.AutoField(primary_key=True)
    name = models.CharField(max_length=100 , blank=True)
    slug = models.CharField(max_length=200 , blank=True)

class Product_Categories(models.Model):
    id  = models.AutoField(primary_key=True)
    product_id  = models.ForeignKey(Products, on_delete=models.DO_NOTHING)
    category_id =  models.ForeignKey(Categories, on_delete=models.DO_NOTHING)

This relationship is then easy to query, and in the example below we annotate each category with the count of related products and order by the annotation so that top performing come first

from django.db.models import Count

Categories.objects.annotate(
    num_products=Count('products')
).order_by('-num_products')

CodePudding user response：

How about something like:

from django.db.models import Count

category_with_the_most_products = Categories.objects.annotate(
    product_count = Count('product_categories_set')
).order_by(
    '-product_count'
).first()

This will create a queryset of all categories with the number of product_categories as the product_count field. Then it will order them in descending order. Finally it will take the first from the list which will be the category with the most product_categories and hence the most products.