我正在使用带有PostgreSQL的Django 1.6,并有以下型号:
# models.py class Game(AbstractContentModel, AbstractScoreModel): name = models.CharField(_("name"), max_length=100, blank=True) developer = models.CharField(_('Developer'), max_length=255) distributor = models.CharField(_('Distributor'), max_length=255, blank=True) # ... reviews = models.ManyToManyField(Review, related_name="games", blank=True, verbose_name=_("Reviews")) videos = models.ManyToManyField(Video, related_name="games", blank=True, verbose_name=_("Videos")) images = models.ManyToManyField(Image, related_name="games", blank=True, verbose_name=_("Gallery"))
我正在尝试获取所有游戏,并且每个游戏都会添加相关视频,评论和图像,如下所示:
# views.py qs = Game.objects.all() qs = qs.annotate(video_count=models.Count('videos')) qs = qs.annotate(review_count=models.Count('reviews')) qs = qs.annotate(image_count=models.Count('images'))
结果查询是:
SELECT "content_game"."id", "content_game"."name", "content_game"."developer", "content_game"."distributor", COUNT("content_game_videos"."video_id") AS "video_count", COUNT("content_game_reviews"."review_id") AS "review_count", COUNT("content_game_images"."image_id") AS "image_count" FROM "content_game" LEFT OUTER JOIN "content_game_videos" ON ( "content_game"."id" = "content_game_videos"."game_id" ) LEFT OUTER JOIN "content_game_reviews" ON ( "content_game"."id" = "content_game_reviews"."game_id" ) LEFT OUTER JOIN "content_game_images" ON ( "content_game"."id" = "content_game_images"."game_id" ) GROUP BY "content_game"."id", "content_game"."name", "content_game"."developer", "content_game"."distributor";
我的问题是 - 为什么GROUP BY子句中有所有选定的字段?最重要的是,除了原始SQL之外,我怎么能摆脱它们呢?
我知道使用它会很好.values()
,但我希望结果查询中的每个字段.如果我只是使用GROUP BY "content_game"."id"
,结果是一样的,但我不知道如何使用它与Django ORM一样.