how to perform count and join in django?

Question

Hi guys,

how do i use the models to perform a join and count query like the one below:

select count(*),site_url from connection_ss 
   join site_ss on to_id_id = site_id 
        where site_ss.source_id = 1 group by site_url order by count desc

Here are my models:

class site(models.Model):
    site_id = models.AutoField(primary_key=True)
    site_url = models.URLField(unique=True)
    human_verified = models.BooleanField(default=False)
    last_entry2 = models.DateTimeField(default='2009-01-01')
    source_id = models.ForeignKey(source)
    author_alias = models.TextField()

class connection(models.Model):
    from_id = models.ForeignKey(site,related_name="from_get")
    to_id = models.ForeignKey(site,related_name = "to_get")

    class Meta:
        db_table = "connection_ss"
        unique_together = (("from_id","to_id"),)

Answer 1

What you are looking for is Aggregation/annotation . With the Count object you could do something like this:

Connection.objects.filter(site__to__source=1).annotate(Count('site_url')).order_by('-count')

Note that This won't work because I didn't really understand what were the objects you tried to fetch, but I hope this will be helpful as a guideline