66

I've got this model:

class Visit(models.Model):
    timestamp  = models.DateTimeField(editable=False)
    ip_address = models.IPAddressField(editable=False)

If a user visits multiple times in one day, how can I filter for unique rows based on the ip field? (I want the unique visits for today)

today = datetime.datetime.today()
yesterday = datetime.datetime.today() - datetime.timedelta(days=1)

visits = Visit.objects.filter(timestamp__range=(yesterday, today)) #.something?

EDIT:

I see that I can use:

Visit.objects.filter(timestamp__range=(yesterday, today)).values('ip_address')

to get a ValuesQuerySet of just the ip fields. Now my QuerySet looks like this:

[{'ip_address': u'127.0.0.1'}, {'ip_address': u'127.0.0.1'}, {'ip_address':
 u'127.0.0.1'}, {'ip_address': u'127.0.0.1'}, {'ip_address': u'127.0.0.1'}]

How do I filter this for uniqueness without evaluating the QuerySet and taking the db hit? 

# Hope it's something like this...
values.distinct().count()
Scott
  • 3,204
  • 3
  • 31
  • 41
  • possible duplicate of [Select distinct values from a table field](http://stackoverflow.com/questions/2466496/select-distinct-values-from-a-table-field) – Mark Mikofski Aug 24 '14 at 07:40
  • @MarkMikofski is not duplicate, the question here is about Django not about plain SQL – Adrian B May 31 '22 at 11:56

3 Answers3

50

What you want is:

Visit.objects.filter(stuff).values("ip_address").annotate(n=models.Count("pk"))

What this does is get all ip_addresses and then it gets the count of primary keys (aka number of rows) for each ip address.

Alex Gaynor
  • 14,353
  • 9
  • 63
  • 113
  • 2
    I don't think I'm totally understanding annotate. As you wrote it, my ValuesQuerySet now has "n":1 appended to each entry. I'm not sure what that's telling me? – Scott Aug 08 '10 at 02:55
  • 10
    The problem is probably Meta.ordering - try this `Visit.objects.filter(stuff).order_by().values("ip_address").annotate(n=models.Count("pk"))` – Greg Sep 30 '13 at 22:53
  • 2
    @Greg Thank you! I new that the `ordering` and `order_by()` causes issues with `distinct` but I didn't know how to solve it - although it is in the QuerySet API docs under [`order_by()`](https://docs.djangoproject.com/en/dev/ref/models/querysets/#order-by) "_If you don’t want any ordering to be applied to a query, not even the default ordering, call `order_by()` with no parameters._" – Mark Mikofski Aug 24 '14 at 07:25
  • What is `pk` in this case? – User Apr 23 '15 at 23:49
  • `pk` - primary key. Unique identifier for each record. – arudzinska Nov 16 '18 at 10:20
35

With Alex Answer I also have the n:1 for each item. Even with a distinct() clause.

It's weird because this is returning the good numbers of items :

Visit.objects.filter(stuff).values("ip_address").distinct().count()

But when I iterate over "Visit.objects.filter(stuff).values("ip_address").distinct()" I got much more items and some duplicates...

EDIT :

The filter clause was causing me troubles. I was filtering with another table field and a SQL JOIN was made that was breaking the distinct stuff. I used this hint to see the query that was really used :

q=Visit.objects.filter(myothertable__field=x).values("ip_address").distinct().count()
print q.query

I then reverted the class on witch I was making the query and the filter to have a join that doesn't rely on any "Visit" id.

hope this helps

Guillaume Gendre
  • 2,504
  • 28
  • 17
  • Is this a question or an answer? – User Apr 24 '15 at 01:08
  • it was a kind of complement to the Alex answer. I tried it, had the same problem than vfxcode, and then I found why. So I thought I should share my findings. 3 years later, I admit my answer was a bit messy and I understand why you ask this ;) – Guillaume Gendre Apr 30 '15 at 17:21
7

The question is different from what the title suggests. If you want set-like behavior from the database, you need something like this.

x = Visit.objects.all().values_list('ip_address', flat=True).distinct()

It should give you something like this for x.

[1.2.3.4, 2.3.4.5, ...]

Where

len(x) == len(set(x))

Returns True

Chris Conlan
  • 2,774
  • 1
  • 19
  • 23