complex query in django where subquery exists

Question

I have a table with many records and am trying to create a complex query and hoping for some help here.

models.py

class Vote(models.Model):
    is_annonymous   = models.BooleanField(default=True)
    vote            = models.IntegerField()

    created_by        = models.ForeignKey(User, null=True, blank=True)
    created_at        = models.DateTimeField(null=True, blank=True)
    ip_address        = models.IPAddressField(null=True, blank=True)


    #content type
    content_type    = models.ForeignKey(ContentType)
    object_id       = models.PositiveIntegerField()
    content_object  = generic.GenericForeignKey('content_type', 'object_id')

query

all_votes = Vote.objects.filter(
    ~Q(created_by   = None),
)

Am trying to return records from Vote where created_by is not null and created_by made his first vote. Meaning, I want to only return the Vote record if user made his first vote.

Am not sure how to do such a thing, usually, on SQL, I would have to do a sub query and count the user votes and if they are equal one then return the record.

Any idea how would I do that with django models?

This question is similar. It might help you: http://stackoverflow.com/questions/327807/django-equivalent-for-count-and-group-by — Vanessa MacDougal, Sep 09 '11 at 17:50
this post is about grouping results from a queryset. In my case, I would like to filter the queryset to return the first entry only, which in a regular sql can be done with subquery count. But, even if i use the sub query count here, I cannot pass the user arguement in the child query as a filter. — Mo J. Mughrabi, Sep 09 '11 at 18:19

score 0 · Answer 1 · answered Sep 10 '11 at 18:11

You can do this grouping with a combination of the .values and .aggregate methods.

from django.db.models import Q, Min
votes = Vote.objects.filter(~Q(created_by=None).values('created_by').aggregate(min=Min('created_at'))

Once you have that, if you want to get the actual Vote instances, you'll need to do another query and use the ids returned above.

For more information on this, check out the Django help section on aggregate and values.

score 0 · Answer 2 · answered Sep 10 '11 at 21:19

0

try to left join Vote to itself on left_vote.created_by==right_vote.created_by and left_vote.id!=right_vote.id and filter the records that have nulls on the right of the join.

answered Sep 10 '11 at 21:19

akonsu

28,824
33
119
194

complex query in django where subquery exists

2 Answers2