Django aggregate queryset - combine the results of two querysets which both contain aggregates

Question

I have the following model in django:

action = models.CharField("Action performed",max_length=200,db_index=True)
    date = models.DateField("Date when event occured",blank=True)
    time = models.TimeField("Time when event occured",blank=True)
    timestamp = models.DateTimeField("Timestamp when event occured",blank=True,null=True,db_index=True)
    username = models.ForeignKey(USER)
    computer = models.ForeignKey(COMPUTER)
    location = models.ForeignKey(LOCATION)

I wish to return data to a HTML page, which has the following columns:

COMPUTER NAME, COUNT(LOGON), COUNT(LOGOFF)

I am using

logOnOffData = LOGONOFF.objects.all()
computerBreakDown = logOnOffData.values("computer__computerName").annotate(Count("timestamp"))

This generates the number of occurrences of each computer being included in the database table. I can easily generate a queryset for each action, but cannot see how to include the results of the two querysets into one.

score 0 · Answer 1 · edited May 23 '17 at 12:10

Would be wonderful to filter on annotations, somethink like:

loging_logut = ( Computer
                 .objects
                 .all()
                 .annotate( longons = Count( action__action = 'logon' ) )
                 .annotate( longouts = Count( action__action = 'logout' ) )

But this syntax is not allowed on django ORM. You can learn about it on FILTERING ON ANNOTATIONS IN DJANGO post.

In quoted article you can learn about some workaround: for example writing your own sql code in extra field.

If performance is not the issue, the most code readable approach is a dictionary like this:

{ 'john s computer': { 'n_logins': 100, 'n_logounts' :99 }, 
  'mathiew s computer': { 'n_logins': 80, 'n_logouts' :80 }, 
  ...
 }

To get this dictionary you can annotate logins and logouts separately, then join it:

#logons = { 'john s computer': 100, 'mathiew s computer': 80, ... }
logons = dict(
               Computer
               .objects
               .filter( action__action = 'logon' )
               .annotate( longons = Count(  ) )
               .values_list( 'name', 'logons' )
         )

#logons = { 'john s computer': 99, 'mathiew s computer': 80, ... }
logouts = dict( ...

#joining dicsts
computers = {}
for computer in Computer.objects.all().values_list( 'name', flat = True):
    computers[computer]['n_logins'] = logons[computer]
    computers[computer]['n_logouts'] = logouts[computer]

Django aggregate queryset - combine the results of two querysets which both contain aggregates

1 Answers1