CODEWITHSUNDEEP
pythondjango
George
George

Reputation: 63

How to add key value pair to django queryset

I want to show stats for account by account in for loop of template. In list of dicts or querysets.

I have 3 models: (1)AdvertisingAccount, (2)Campaign, UserProfile. I need to sum() clicks and views of Campaigns that belongs to certain AdvertisingAccounts by Foreign Key. (AdvertisingAccount also belongs to UserProfile )

Im trying to retrieve, calculate and add new key-value to dicts of queryset, but seems like I cant do it in dict method…

views.py

def ad_accounts(request):

    user = request.user
    accounts = user.userprofile.advertising_accounts.all()

    accounts = accounts.values()

    for d in accounts:
        d.update(('clicks', d.campaigns.all().aggregate(Sum('clicks')) )  for k,v in d.items())
        d.update(('views', d.campaigns.all().aggregate(Sum('views')) )  for k,v in d.items())

models.py


class AdvertisingAccount(models.Model):
    balance = models.DecimalField(max_digits=12, decimal_places=2, default=0, blank=True, null=True)
    foreign_system_name = models.CharField(max_length=30, blank=True, default=None)
    foreign_system_id = models.IntegerField(default=None, blank=True, null=True)
    def __str__(self):
        return self.foreign_system_name + '--' + str(self.foreign_system_id)


class Campaign(models.Model):
    userprofile = models.ForeignKey('users.UserProfile', on_delete=models.PROTECT, related_name='campaigns')
    trade = models.ForeignKey('payments.Trade', on_delete=models.PROTECT, related_name='campaigns')
    ad_account = models.ForeignKey('AdvertisingAccount', on_delete=models.PROTECT, related_name='campaigns')
    views = models.IntegerField(default=0, blank=True, null=True)
    clicks = models.IntegerField(default=0, blank=True, null=True)
    ad_spent = models.DecimalField(max_digits=12, decimal_places=2, default=0, blank=True, null=True)

    def __str__(self):
        return str(self.userprofile) + '--' + str(self.ad_account.foreign_system_name) + '--' + str(self.trade.ad_budget)

template

{% for i in accounts %}
  {{i.clicks}}
  {{i.views}}
{% endfor %}

I got error :'dict' object has no attribute 'campaigns'

As I cant retrieve filtered data from templates, Im trying to construct custom queryset or list of dicts. Does the whole way correct? How to solve my problem?

Upvotes: 0

Views: 3480

Answers (1)

willeM_ Van Onsem
willeM_ Van Onsem

Reputation: 476557

Please do not annotate manually, you can simply use Django's .annotate(..) method [Django-doc]. You even used a generator here where it will result in querying a lot of times for a single Account model.

from django.db.models import Sum

accounts = user.userprofile.advertising_accounts.annotate(
    clicks=Sum('campaigns__clicks'),
    views=Sum('campaigns__views')
)

The above will generate a QuerySet of AdvertisingAccounts, and with extra attributes .clicks and .views. The error is in fact entirely because you did not use AdvertisingAccounts in the first place, but dictionaries (that originate from the .values(), and you thus lose the context of the relations).

The annotation will run in a single query, where Django will make a query that looks like:

SELECT advertising_acount.*,
       SUM(campaign.clicks) AS clicks,
       SUM(campaign.views) AS views
FROM advertising_acount
LEFT OUTER JOIN campaign ON campaign.ad_account_id = advertising_acount.id
GROUP BY advertising_acount.id

This is more efficient than aggregating per AdvertisingAccount, since then you would hit the database 2n+1 times here with n the number of AdvertisingAcounts.

Usually it is therefore nearly always better to use model instance over a dictionary: a model class allows you to define methods, properties, it will add relations, etc. It also holds the type of the object (for example an AdvertisingAccount) whereas two dictionaries with the same keys do not per se originate from the same models. A dictionary is just a flat stucture that maps field names to the corresponding database value. You thus "lose" a lot of context.

Upvotes: 6

Related Questions