CODEWITHSUNDEEP
djangodjango-orm
outoftime
outoftime

Reputation: 765

Complex django queryset annotation with multiple subqueries

Here is some annotation list:

    count_set = {
        'num_followers': 'following_followinguser_leader',
        'num_followings': 'following_followinguser_follower',
    }

    for k, v in count_set.iteritems():
        qs = qs.annotate(**{k: models.Count(v, distinct=True)})
    return qs

I'm looking for solution of adding all field which will be a number of unique elements in num_followers and num_followings.

Problem is that is should be fast enough, the best way is pure SQL, but what about django ORM?

UPD: PostgreSQL table schema for following

            Table "public.following_followinguser"
     Column      |           Type           |  Modifiers                               
-----------------+--------------------------+------------------------------
id               | integer                  | not null
follower_id      | integer                  | not null
leader_id        | integer                  | not null

Where follower and leader ids are links to custom user model inherited from AbstractUser. following_followinguser_leader is related name for leader field of model above.

e.g. Let followers will be users with ids: 1, 2, 3 and user 4 is follower for users with ids 1, 5, 6. Than all should be: 1, 2, 3, 5, 6. All I need is number of that, i.e. 5.

Upvotes: 0

Views: 1053

Answers (1)

Árni St. Steinunnarson
Árni St. Steinunnarson

Reputation: 1610

The SQL generated will probably be as tight as you can make it via the Django ORM. You can always validate that by printing out qs.query.sql_with_params() and seeing the generated SQL. If you need to, you can add indexes to the columns to speed up the count (postgres 9.2+).

Upvotes: 1

Related Questions