Complex django queryset annotation with multiple subqueries

Question

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.

Árni St. Steinunnarson · Accepted Answer · 2014-10-27 14:07:52Z

1

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+).

edited Oct 27, 2014 at 14:07

answered Oct 27, 2014 at 13:37

Árni St. Steinunnarson

1,62012 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Árni St. Steinunnarson Over a year ago

Then I must misunderstand your models. Can you add them to your question?

Árni St. Steinunnarson Over a year ago

I understand now. Your models have db_column for each of the fields. I'll edit my answer.

outoftime Over a year ago

Well I need TOTAL to be equal to number of elements in total set TOTAL = |FOLLOWERS + FOLLOWINGS|

Árni St. Steinunnarson Over a year ago

Then I think you want the same answer as this SO question. Only way I see it being done is iterating over the queryset and doing it by hand, or raw SQL.

outoftime Over a year ago

Let us continue this discussion in chat.

Collectives™ on Stack Overflow

Complex django queryset annotation with multiple subqueries

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related