CODEWITHSUNDEEP
djangopostgresqldjango-modelsdjango-orm
guytamir1
guytamir1

Reputation: 73

Subquery in select Django

Trying to run a complicated query in Django over Postgresql.

These are my models:

class Link(models.Model):
    short_key = models.CharField(primary_key=True, max_length=8, unique=True, blank=True)
    long_url = models.CharField(max_length=150)

class Stats_links_ads(models.Model):
    link_id = models.ForeignKey(Link, related_name='link_viewed', primary_key=True)
    ad_id = models.ForeignKey(Ad, related_name='ad_viewed')
    views = models.PositiveIntegerField()
    clicks = models.PositiveIntegerField()

I want to run using the Django ORM a query which will translate into something like so:

select a.link_id, sum(a.clicks), sum (a.views), (select long_url from links_link b where b.short_key = a.link_id_id)
from links_stats_links_ads a
group by a.link_id_id;

If i exclude the long_url field that I need I can run this code and it will work:

Stats_links_Ads.objects.all().values('link_id').annotate(Sum('views'), Sum('clicks'))

I don't know how to add the subquery in the select statement.

Thanks

Upvotes: 0

Views: 1477

Answers (1)

Enrico
Enrico

Reputation: 10685

You can see the raw sql behind your queries using the query attribute of Queryset.

For example, look at the sql behind my first answer using select_related, it's clear the generated sql doesn't behave as expected and accessing the long_url will result in additional queries.

Take 2

You can follow relationships using double underscore notation like this

qs = Stats_links_ads.objects
        .values('link_id', 'link_id__long_url')
        .annotate(Sum('views'), Sum('clicks'))
str(qs.query)
'SELECT 
    "stackoverflow_stats_links_ads"."link_id_id", 
    "stackoverflow_link"."long_url", 
    SUM("stackoverflow_stats_links_ads"."clicks") AS "clicks__sum",
    SUM("stackoverflow_stats_links_ads"."views") AS "views__sum"
FROM "stackoverflow_stats_links_ads" 
    INNER JOIN "stackoverflow_link" 
    ON ("stackoverflow_stats_links_ads"."link_id_id" = "stackoverflow_link"."short_key")
GROUP BY 
    "stackoverflow_stats_links_ads"."link_id_id", 
    "stackoverflow_link"."long_url"'

I'm not working with any data, so I haven't verified it, but the sql looks right.

Take 1

Does not work

Can't you use .select_related? [docs]

qs = Stats_links_Ads.objects.select_related('link')
     .values('link_id').annotate(Sum('views'), Sum('clicks'))
str(qs.query)
'SELECT 
    "stackoverflow_stats_links_ads"."link_id_id", 
    SUM("stackoverflow_stats_links_ads"."clicks") AS "clicks__sum", 
    SUM("stackoverflow_stats_links_ads"."views") AS "views__sum" 
FROM "stackoverflow_stats_links_ads" 
GROUP BY "stackoverflow_stats_links_ads"."link_id_id"'

Upvotes: 1

Related Questions