1

I am new to django and SQL queries. I am trying some annotation with django. but unable to get results

+-----------------------+-----------+---------------------+
| email                 | event     | event_date          |
|-----------------------+-----------+---------------------|
| hector@example.com    | open      | 2017-01-03 13:26:13 |
| hector@example.com    | delivered | 2017-01-03 13:26:28 |
| hector@example.com    | open      | 2017-01-03 13:26:33 |
| hector@example.com    | open      | 2017-01-03 13:26:33 |
| tornedo@example.com   | open      | 2017-01-03 13:34:53 |
| tornedo@example.com   | 1         | 2017-01-03 13:35:22 |
| tornedo@example.com   | open      | 2016-09-05 00:00:00 |
| tornedo@example.com   | open      | 2016-09-17 00:00:00 |
| sparrow@example.com   | open      | 2017-01-03 16:05:36 |
| tornedo@example.com   | open      | 2017-01-03 20:12:15 |
| hector@example.com    | open      | 2017-01-03 22:06:47 |
| sparrow@example.com   | open      | 2017-01-09 19:46:26 |
| sparrow@example.com   | open      | 2017-01-09 19:47:59 |
| sparrow@example.com   | open      | 2017-01-09 19:48:28 |
| sparrow@example.com   | delivered | 2017-01-09 19:52:24 |
+-----------------------+-----------+---------------------+

I have a table like this which contains email activity. I want to find who opened recently and also i want to count of each event happened. I want results exactly like

 email            | open  | click   | delivered   | max_open_date 
hector@example.com    4   <null>      1            2017-01-03 22:06:47
sparrow@example.com   3   <null>      1            2017-01-09 19:48:28
tornedo@example.com   4     1        <null>        2017-01-03 20:12:15

my model looks:

class EmailEvent(models.Model):
    event = models.TextField(blank=True, null=True)
    email = models.TextField(blank=True, null=True)
    event_date = models.DateTimeField(blank=True, null=True)

i tried the following code. it giving correct count for open, click, delivered but giving wrong result for max_open_date. but i don't know why

EmailEvent.objects.values('email').annotate(
    max_open_date=Case(When(event='open', then=Max('event_date')))),
    open=Sum(Case(When(event='open',then=1),output_field=IntegerField())),
    click=Sum(Case(When(event='click',then=1),output_field=IntegerField())),
    open=Sum(Case(When(event='open',then=1),output_field=IntegerField())),
    delivered=Sum(Case(When(event='delivered',then=1),output_field=IntegerField())),
)

Help me to get exact results i want. sorry for my bad english. Thanks!

REDDY PRASAD
  • 1,309
  • 2
  • 14
  • 29
  • I tried only the code i given. only problem with max_open_date everything working fine. – REDDY PRASAD Feb 01 '17 at 09:51
  • Are you getting any exception for that? If you are not getting any exception try keeping USE_TZ=False in your settings. Django's datetimefiled filtering will depend on time zone. You can get the results by disabling the timezone in your settings. – MicroPyramid Feb 01 '17 at 09:57

1 Answers1

3

I do not use django, but probably you need something like this:

max_open_date=Max(Case(When(event='open', then='event_date')))
Roman Tkachuk
  • 3,096
  • 1
  • 16
  • 15
  • Thanks Bro! It works. what is difference between code i wrote and yours ? – REDDY PRASAD Feb 02 '17 at 06:35
  • You need to run aggregate function max for event_date with filter (event = 'open'). That mean than you choose max value of event_date from filtered data per each email. But looks like your construction run filter and after that run max for each row separately. – Roman Tkachuk Feb 02 '17 at 07:40
  • I'm getting a `'unicode' object has no attribute 'tzinfo'` error for this. My 'event_date` equivalent is definitely a DateTimeField. – jozxyqk Apr 04 '17 at 23:09
  • http://stackoverflow.com/questions/16492031/how-to-fix-this-error-unicode-object-has-no-attribute-tzinfo – Roman Tkachuk Apr 05 '17 at 21:09