Specific complex SQL query and Django ORM?

Question

I have a set of tables that contain content that is created and voted on by users.

Table content_a

id         /* the id of the content */
user_id    /* the user that contributed the content */
content    /* the content */

Table content_b

id
user_id
content

Table content_c

id
user_id
content

Table voting

user_id         /* the user that made the vote */
content_id      /* the content the vote was made on */
content_type_id /* the content type the vote was made on */
vote            /* the value of the vote, either +1 or -1 */

I want to be able to select a set of users and order them by the sum of the votes on the content they have produced. For example,

SELECT * FROM users ORDER BY <sum of votes on all content associated with user>

Is there a specific way this can be achieved using Django's ORM, or do I have to use a raw SQL query? And what would the most efficient way be to achieve this in raw SQL?

Given a vote in your voting table, how can you tell to which content table it relates? What if the content_id exists in more than one table? — eggyal
– eggyal, Commented May 26, 2012 at 6:11

Community · Accepted Answer · 2017-05-23 10:09:06Z

7

+50

Update

Assuming the models are

from django.contrib.contenttypes import generic
from django.contrib.contenttypes.models import ContentType


class ContentA(models.Model):
    user = models.ForeignKey(User)
    content = models.TextField()

class ContentB(models.Model):
    user = models.ForeignKey(User)
    content = models.TextField()

class ContentC(models.Model):
    user = models.ForeignKey(User)
    content = models.TextField()

class GenericVote(models.Model):
    content_type = models.ForeignKey(ContentType)
    object_id = models.PositiveIntegerField()
    content_object = generic.GenericForeignKey()
    user = models.ForeignKey(User)
    vote = models.IntegerField(default=1)

Option A. Using GenericVote

GenericVote.objects.extra(select={'uid':"""
CASE
WHEN content_type_id = {ct_a} THEN (SELECT user_id FROM {ContentA._meta.db_table} WHERE id = object_id)
WHEN content_type_id = {ct_b} THEN (SELECT user_id FROM {ContentB._meta.db_table} WHERE id = object_id)
WHEN content_type_id = {ct_c} THEN (SELECT user_id FROM {ContentC._meta.db_table} WHERE id = object_id)
END""".format(
ct_a=ContentType.objects.get_for_model(ContentA).pk,
ct_b=ContentType.objects.get_for_model(ContentB).pk,
ct_c=ContentType.objects.get_for_model(ContentC).pk,
ContentA=ContentA,
ContentB=ContentB,
ContentC=ContentC
)}).values('uid').annotate(vc=models.Sum('vote')).order_by('-vc')

The above ValuesQuerySet,(or use values_list()) gives you a sequence of IDs of User()s in the order of descending votes count. You could then use it to fetch top users.

Option B. Using User.objects.raw

When I use User.objects.raw, I got almost same query w/ the answer given by forsvarir :

User.objects.raw("""
SELECT "{user_tbl}".*, SUM("gv"."vc") as vote_count from {user_tbl},
    (SELECT id, user_id, {ct_a} AS ct FROM {ContentA._meta.db_table} UNION
     SELECT id, user_id, {ct_b} AS ct FROM {ContentB._meta.db_table} UNION
     SELECT id, user_id, {ct_c} as ct FROM {ContentC._meta.db_table}
    ) as c,
   (SELECT content_type_id, object_id, SUM("vote") as vc FROM {GenericVote._meta.db_table} GROUP BY content_type_id, object_id) as gv
WHERE {user_tbl}.id = c.user_id
    AND gv.content_type_id = c.ct
    AND gv.object_id = c.id
GROUP BY {user_tbl}.id
ORDER BY "vc" DESC""".format(
    user_tbl=User._meta.db_table, ContentA=ContentA, ContentB=ContentB,
    ContentC=ContentC, GenericVote=GenericVote, 
    ct_a=ContentType.objects.get_for_model(ContentA).pk,
    ct_b=ContentType.objects.get_for_model(ContentB).pk,
    ct_c=ContentType.objects.get_for_model(ContentC).pk
))

Option C. Other possible ways

De-normalize vote_count to User or profile model, for example, UserProfile, or other relative model, as suggested by Michael Dunn. This behaves much better if you access vote_count on-fly frequently.
Build a DB view which does the UNIONs for you, then map a model to it, this could make the construction of the query easier.
Sort in Python, usually it's best way to work for large-scale data, because of dozen of toolkits and extension ways.

You need some Django Models mapping those tables before use Django ORM to query. Assuming they are User and Voting models that matching users and voting tables, you could then

User.objects.annotate(v=models.Sum('voting__vote')).order_by('v')

edited May 23, 2017 at 10:09

CommunityBot

11 silver badge

answered May 20, 2012 at 15:14

okm

23.9k5 gold badges86 silver badges92 bronze badges

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

3 Comments

mburke13 Over a year ago

This won't work, the voting table column 'user_id' is associated with the vote made by a user. I want to sum the votes on a user's content, not made by the user.

okm Over a year ago

@Matt I see. What're models for content_a, content_b and content_c then?

mburke13 Over a year ago

The models are pretty generic. I think the only important things to note are that each content model is related to a user by a ForeignKey(User) relation and that each content model is related to a vote in the voting table by a GenericForeignKey relation with the content's id and the content's content_type. I think what I want to achieve is too complex for Django's ORM, so I'm first trying to figure out the best way to do it in SQL. Because of that, I only gave database table structures instead of Django models. If there is a way to do it in Django, I'd be happy to hear it.

forsvarir · Accepted Answer · 2012-05-29 18:02:02Z

For a raw SQL solution, I've created a rough replication of your problem on ideone here

Data setup:

create table content_a(id int, user_id int, content varchar(20));
create table content_b(id int, user_id int, content varchar(20));
create table content_c(id int, user_id int, content varchar(20));
create table voting(user_id int, content_id int, content_type_id int, vote int);
create table users(id int, name varchar(20));
insert into content_a values(1,1,'aaaa');
insert into content_a values(2,1,'bbbb');
insert into content_a values(3,1,'cccc');
insert into content_b values(1,2,'dddd');
insert into content_b values(2,2,'eeee');
insert into content_b values(3,2,'ffff');
insert into content_c values(1,1,'gggg');
insert into content_c values(2,2,'hhhh');
insert into content_c values(3,3,'iiii');
insert into users values(1, 'first');
insert into users values(2, 'second');
insert into users values(3, 'third');
insert into users values(4, 'voteonly');

-- user 1 net votes (2)
insert into voting values (1, 1, 1, 1);
insert into voting values (2, 3, 1, -1);
insert into voting values (3, 1, 1, 1); 
insert into voting values (4, 2, 1, 1); 

-- user 2 net votes (3)
insert into voting values (1, 2, 2, 1);
insert into voting values (1, 1, 2, 1);
insert into voting values (2, 3, 2, -1);
insert into voting values (4, 2, 2, 1);
insert into voting values (4, 2, 3, 1);

-- user 3 net votes (-1)
insert into voting values (2, 3, 3, -1);

I've basically assumed that content_a has a type of 1, content_b has a type of 2 and content_c has a type of 3. Using raw SQL, there seems to be two obvious approaches. The first is to union all of the content together, then join it with the users and voting tables. I've tested this approach below.

select users.*, sum(voting.vote)
from users, 
    voting, (
        SELECT     id, 1 AS content_type_id, user_id
        FROM         content_a
        UNION
        SELECT     id, 2 AS content_type_id, user_id
        FROM         content_b
        UNION
        SELECT     id, 3 AS content_type_id, user_id
        FROM         content_c) contents
where contents.user_id = users.id
and voting.content_id = contents.id
and voting.content_type_id = contents.content_type_id
group by users.id
order by sum(voting.vote) desc;

The alternative would seem to be to outer join the content tables to the voting tables, without the union step. This may be more performant, but I haven't been able to test it because visual studio keeps rewriting my sql for me... I'd expect the SQL to look something like this (but I haven't tested it):

select users.*, sum(voting.vote)
from users, voting, content_a, content_b, content_c
where users.id = content_a.user_id (+)
and users.id = content_b.user_id (+)
and users.id = content_c.user_id (+)
and ((content_a.id = voting.content_id and voting.content_type_id = 1) OR
     (content_b.id = voting.content_id and voting.content_type_id = 2) OR
     (content_c.id = voting.content_id and voting.content_type_id = 3))
group by users.id
order by sum(voting.vote) desc;

the 1 in SELECT id, 1 AS content_type_id, user_id FROM content_c might be a typo?
@okm: Thanks you're right it should have been a 3, I've updated it.

Michael Dunn · Accepted Answer · 2012-05-29 14:12:33Z

I would do this using precalculated values. First make a separate table to store the votes that each user has received:

class VotesReceived(models.Model):
    user = models.OneToOneField(User, primary_key=True)
    count = models.IntegerField(default=0, editable=False)

then use a post_save signal to update the count every time a vote is made:

def update_votes_received(sender, instance, **kwargs):
    # `instance` is a Voting object
    # assuming here that `instance.content.user` is the creator of the content
    vr, _ = VotesReceived.objects.get_or_create(user=instance.content.user)
    # you should recount the votes here rather than just incrementing the count
    vr.count += 1 
    vr.save()

models.signals.post_save.connect(update_votes_received, sender=Voting)

Usage:

user = User.objects.get(id=1)
print user.votesreceived.count

If you already have data in your database you'd have to update the vote counts manually the first time of course.

Collectives™ on Stack Overflow

Specific complex SQL query and Django ORM?

3 Answers 3

3 Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related