Django link multiple table

Question

I have four models as follows:

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)

I want to list all deals for the current month and its corresponding number of views per gender.

so far I've tried the following; i can get all the info but it seems i have multiple hits of the database:

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)

Is there any way i can optimise my queries to get the number of views per gender for a deal of a specific store?

willeM_ Van Onsem · Accepted Answer · 2019-05-19 12:05:07Z

1

Yes, we can do some filtering in the related client_profile model, and then use a .annotate(..) to count the number of distinct ids.

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')

This will result in a QuerySet that contains dictionaries with the gender, and the number of client_profiles, like:

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

If there are no client_profiles with a given gender that have seen the deal, then it is not part of the QuerySet.

answered May 19, 2019 at 12:05

willeM_ Van Onsem

482k33 gold badges483 silver badges624 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Django link multiple table

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related