Django链接多个表

Question

我有四个模型，如下：

class deals_view(models.Model):
    deal = models.ForeignKey(dealsModel, on_delete=models.CASCADE, related_name='deal_view')
    client = models.ForeignKey(client_profileModel, on_delete=models.CASCADE)
    client_ip = models.GenericIPAddressField(protocol='both')
    date_added = models.DateField(auto_now_add=True)

class client_profile(models.Model):
    GENDER_NAME = (
        ('M', 'Male'),
        ('F', 'Female'),
        ('O', 'Others')
    )
    user = models.ForeignKey(User, unique=True, on_delete=models.CASCADE)
    gender = models.CharField(max_length=1, choices=GENDER_NAME, null=True)
    address = models.CharField(max_length=100, null=True)
    dob = models.DateField(null=True)
class deals(models.Model):
    GENDER_NAME = (
        ('M', 'Male'),
        ('F', 'Female'),
        ('O', 'Others'),
        ('A', 'All'),
    )
    AGE_RANGE = (
        ('A1', '18-25'),
        ('A2', '25-40'),
        ('A3', '40-55'),
        ('A4', '55-100'),
        ('A5', '18-100'),
        ('AL', '13-100'),
        ('T1', '13-18')
    )
    store = models.ForeignKey(storesModel, on_delete=models.CASCADE, related_name='deals_store')
    title = models.CharField(max_length=30)
    description = models.CharField(max_length=160)
    price = models.DecimalField(max_digits=6, decimal_places=2)
    category = models.ForeignKey(categoriesModel, on_delete=models.PROTECT)
    targeted_gender = models.CharField(max_length=1, choices=GENDER_NAME)
    targeted_age = models.CharField(max_length=2, choices=AGE_RANGE)
    is_active = models.BooleanField(default=False)
    created_date = models.DateField(auto_now_add=True)
    expiry_date = models.DateField(default=(dt.now() + td(days=30)))

class stores(models.Model):
    user = models.ForeignKey(User, on_delete=models.CASCADE)
    name = models.CharField(max_length=50, unique=True)
    about = models.CharField(max_length=300)
    company_name = models.CharField(max_length=100)

我想列出本月的所有交易及其每个性别的相应视图数量。

到目前为止，我已经尝试了以下内容；我可以获取所有信息，但是看来我有多个数据库的命中：

self.storeObj = storesModel.objects.prefetch_related('deals_store').get(id=storeid)
self.deals_store = self.storeObj.deals_store
segmentedInfo = self.deals_store.all().prefetch_related('deal_view').filter(deal_view__date_added__range=((datetime.today() - timedelta(days=30)), datetime.now())).distinct()
            for seg in segmentedInfo:
                for x in seg.deal_view.select_related('client').distinct():
                    print(x.client.gender)

有什么方法可以优化我的查询以获取特定商店交易的每个性别视图数量？

Answer 1

是的，我们可以在相关的client_profile模型中进行一些过滤，然后使用.annotate(..)来计算不同ID的数量。

from django.db.models import Count
client_profile.objects.filter(
    deals_view__deal__store_id=storeid,
    deals_view__date_added__range=(datetime.today()-timedelta(days=30), datetime.now())
).values('gender').annotate(
    n=Count('id', distinct=True)
).order_by('gender')

这将导致一个 QuerySet，其中包含具有性别的字典和client_profile s的数量，例如：

<QuerySet [
    {'gender': 'M', 'n': 1425},
    {'gender': 'M', 'n': 1302},
    {'gender': 'O', 'n': 173}
]>

如果没有具有给定性别的client_profile s，那么它是不是QuerySet的一部分。