Django queries when Joining the same table on itself

Question

I find at my job I often have to join the same table on itself, partly because I have no control of the DB design, and partly because things are wanted pretty custom most of the time.

Select distinct t.label, z.dupCount
    From optevents t
    Join
      (select label, Count(*) dupCount
          from optevents 
          group By label
          Having Count(*) > 1) z
    On z.label = t.label
    where t.campaignId = 100
    order By dupCount Desc

I'm curious how I can convert this to a Django ORM lookup?

The Model definition is pretty simple, no foreign keys at all.

This is untested so I'll leave it as a comment for the moment: `OptEvents.objects.filter(campaign__pk=100).values("label").annotate(dupCount=models.Count("label")).order_by("dupCount")` this is using [a special type of `annotations`](https://docs.djangoproject.com/en/dev/topics/db/aggregation/#values) — Timmy O'Mahony, Mar 13 '13 at 22:23
Problem with that is I'm trying to find the count of different labels. I.e. label can be A | B | C, and I need the count of A, count of B and count of C — Sam Hammamy, Mar 13 '13 at 22:32

score 3 · Answer 1 · answered Mar 13 '13 at 22:41

You should be able to achieve this with a mix of annotate and values

OptEvents \
    .objects \
    .filter(campaign__pk=100) \
    .values("label") \
    .annotate(dupCount=mod‌els.Count("label")) \
    .order_by("dupCount")

Here's a good blog post on this by Daniel Roseman

Django queries when Joining the same table on itself

1 Answers1