sql - 具体复杂的SQL查询和Django ORM？

Question

我有一组表格，其中包含由用户创建和投票的内容。

表 content_a

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

表 content_b

id
user_id
content

表 content_c

id
user_id
content

表 投票

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 */

我希望能够选择一组用户，并根据他们制作的内容的投票总和对他们进行排序。例如，

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

有没有一种特定的方法可以使用 Django 的 ORM 来实现，或者我是否必须使用原始 SQL 查询？在原始 SQL 中实现这一目标的最有效方法是什么？

Answer 1

更新

假设模型是

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)

选项 A. 使用 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')

以上 ValuesQuerySet ,(或使用 values_list() )为您提供 User() 的 ID 序列s 按票数降序排列。然后，您可以使用它来获取顶级用户。

选项 B. 使用 User.objects.raw

当我使用 User.objects.raw ，我得到了与 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
))

选项 C. 其他可能的方式

去规范化 vote_count至 User或配置文件模型，例如 UserProfile ，或其他相关模型，如 suggested by Michael Dunn .如果您访问 vote_count，这会表现得更好经常飞行。

构建一个执行 UNION 的数据库 View s ，然后将模型映射到它，这可以使查询的构建更容易。

在 Python 中排序，通常是处理大规模数据的最佳方式，因为有很多工具包和扩展方式。

在使用 Django ORM 进行查询之前，您需要一些 Django 模型来映射这些表。假设它们是 User和 Voting匹配的型号 users和 voting表，然后你可以

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