CODEWITHSUNDEEP
djangopostgresqlpython-3.x
Brandon Taylor
Brandon Taylor

Reputation: 34593

Avoid repeated select statement when using "extra" clause in Django ORM

I need to be able to retrieve a sum of related records for display purposes, and also order by the sum of the counts of three related models. Currently, I'm solving this by using an "extra" clause:

results = Poll.visible.extra(
    select={
        'f_count':
        """
        SELECT COUNT('id') FROM polls_forecast
        WHERE polls_forecast.poll_id = polls_poll.id
        """,
        'total':
        """
        (SELECT COUNT('id') FROM polls_forecast
         WHERE polls_forecast.poll_id = polls_poll.id) +
        (SELECT COUNT('id') FROM polls_pollcomment
         WHERE polls_pollcomment.poll_id = polls_poll.id) +
        (SELECT COUNT('id') FROM polls_favoritedpoll
         WHERE polls_favoritedpoll.poll_id = polls_poll.id)
        """
    }
).order_by('-total')[:100]

Is it possible to alias f_count so I don't have to do the same count in the total select statement? Referencing f_count in the total select raises a "column does not exist" error.

I'm using Postgres 9.4, Django 1.7.5, Python 3.4.

Upvotes: 0

Views: 144

Answers (1)

schillingt
schillingt

Reputation: 13731

Not by using .extra. You'll need to use raw. Here are the docs on it.

If you do decide to go that route, you'll have to do the filter that's involved in visible in that raw SQL query as well. I'm not 100% positive on what RawQuerySet provides compared to QuerySet.

Upvotes: 1

Related Questions