Django LEFT JOIN，有效吗? [英] Django LEFT JOIN, efficiently?

问题描述

假设我有这个模型:

class PhotoAlbum(models.Model):
    title = models.CharField(max_length=128)
    author = models.CharField(max_length=128)

class Photo(models.Model):
    album = models.ForeignKey('PhotoAlbum')

我想执行以下查询:找到10个名称以'The'开头的相册，然后将这些相册中的所有照片给我."

在SQL中，我可以这样做:

SELECT * FROM
    (SELECT * FROM photoalbum WHERE title LIKE 'The%' LIMIT 10) AS selected_albums
LEFT JOIN photo ON photo.album_id = selected_albums.id

我的问题是，如何在Django中做到这一点? (无需为每个相册触发查询！) 我认为这是一个相当普遍的要求，我不敢相信没有任何方法可以做到. /p>

如果没有Django-ey方法，我将解决如何使用原始SQL在Django中实现它?".

有些东西行不通:

• select_related();对于 forward ForeignKey关系，这是向后的.
• prefetch_related(); 实际上，这确实有效！至少对于ForeignKey一级.
• PhotoAlbum.photo_set;会触发对每个专辑的查询.

• 我最近的是:

  相册= PhotoAlbum.objects.all()[:10] photos = Photo.objects.filter(album__in = albums)

但是可悲的是，它在MySQL上无法正常工作，而且有人告诉我，使用LEFT JOIN比使用它创建的WHERE ... IN (SELECT ...)类型查询更好.

编辑

我找到了一个 3岁的邮件列表帖子，关于此问题.里面没有解决办法.

6岁的错误报告说，他们不会修复.除了那不是它的工作方式"之外，没有其他原因.显然在RoR中是可能的.

解决方案

这将起作用，并且仅执行两个查询. prefetch_related适用于反向FK，实际上是为以下目的创建的:

for album in PhotoAlbum.objects.filter(title__startswith='The').prefetch_related('photo_set')[:10]:
    print album.photo_set.all()

Suppose I have this model:

class PhotoAlbum(models.Model):
    title = models.CharField(max_length=128)
    author = models.CharField(max_length=128)

class Photo(models.Model):
    album = models.ForeignKey('PhotoAlbum')

And I want to do this query: "Find 10 albums whose name starts with 'The', and then give me all the photos in those albums."

In SQL I could do it something like this:

SELECT * FROM
    (SELECT * FROM photoalbum WHERE title LIKE 'The%' LIMIT 10) AS selected_albums
LEFT JOIN photo ON photo.album_id = selected_albums.id

My question is, how can I do this in Django? (WITHOUT TRIGGERING A QUERY FOR EACH ALBUM!) I assume this is a fairly common requirement, and I can't believe there isn't some way to do it.

If there is no Django-ey way, I will settle for "how can I implement this in Django using raw SQL?".

Here are some things which will not work:

• select_related(); that is for forward ForeignKey relationships, this is backwards.
• prefetch_related(); also for forward relationships. Edit: Actually this does work! At least for one level of ForeignKeys.
• PhotoAlbum.photo_set; that triggers a query for each album.

• The closest I have got is:

  albums = PhotoAlbum.objects.all()[:10] photos = Photo.objects.filter(album__in=albums)

But it doesn't work on MySQL sadly, and I've been told it is better to use LEFT JOIN's than the WHERE ... IN (SELECT ...) type query that this creates.

Edit

I found a 3 year old mailing list post about the problem. No solution therein.

A 6 year old bug report saying they won't fix it. No reason given other than "that's not how it works". Apparently it is possible in RoR though.

解决方案

This will work and only do two queries. prefetch_related works for reverse FKs, that's actually what it was created for:

for album in PhotoAlbum.objects.filter(title__startswith='The').prefetch_related('photo_set')[:10]:
    print album.photo_set.all()

这篇关于Django LEFT JOIN，有效吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！