Search code examples
sqldatabasedjangomodelsinner-join

Performing INNER JOIN, GROUP BY, and COUNT on Django models

I am having problems understanding how to make complex queries (or even simple ones) using Django models. I am looking to do an inner join, group by, and count in one statement using django models.

Example:

Select ab.userid, count(ab.userid) as bids, u.username 
from auctionbids ab 
inner join users u on ab.userid=u.id 
group by ab.userid 
order by numbids desc;

This type of query is very common and straight forward so I have to imagine it can be done with django models but it is not apparent from the documentation.

edit: added models

class Users(models.Model):
    id = models.IntegerField(primary_key=True)
    username = models.CharField(max_length=150)

class Auctionbids(models.Model):
    id = models.IntegerField(primary_key=True)
    user = models.ForeignKey(Users)
Solution

  • If you post your models.py file I can probably give you a more precise answer, but I think what you want is the Django Aggregration API

    You would use it something like this:

    from django.db.models import Count
User.objects.all().annotate(bids=Count('auctionbids')).order_by('bids')