假设我有以下模型:
class Image(models.Model):
image = models.ImageField(max_length=200, upload_to=file_home)
content_type = models.ForeignKey(ContentType)
object_id = models.PositiveIntegerField()
content_object = generic.GenericForeignKey()
class Article(models.Model):
text = models.TextField()
images = generic.GenericRelation(Image)
class BlogPost(models.Model):
text = models.TextField()
images = generic.GenericRelation(Image)
什么是最高效的处理器和内存方式,以查找至少有一张图片附加在其中的所有文章?
我已经做了这个:
Article.objects.filter(pk__in=Image.objects.filter(content_type=ContentType.objects.get_for_model(Article)).values_list('object_id', flat=True))
这个方法可行,但除了难看之外,还需要很长时间。
我猜想使用原始SQL可能有更好的解决方案,但那超出了我的能力范围。就以上面的代码而言,所生成的SQL如下:
SELECT `issues_article`.`id`, `issues_article`.`text` FROM `issues_article` WHERE `issues_article`.`id` IN (SELECT U0.`object_id` FROM `uploads_image` U0 WHERE U0.`content_type_id` = 26 ) LIMIT 21
编辑: czarchaic的建议语法更加优雅,但性能更差(更慢)。他的查询生成的SQL如下:
SELECT DISTINCT `issues_article`.`id`, `issues_article`.`text`, COUNT(`uploads_image`.`id`) AS `num_images` FROM `issues_article` LEFT OUTER JOIN `uploads_image` ON (`issues_article`.`id` = `uploads_image`.`object_id`) GROUP BY `issues_article`.`id` HAVING COUNT(`uploads_image`.`id`) > 0 ORDER BY NULL LIMIT 21
编辑: 感谢Jarret Hardie!以下是他显而易见的解决方案生成的SQL代码:
SELECT DISTINCT `issues_article`.`id`, `issues_article`.`text` FROM `issues_article` INNER JOIN `uploads_image` ON (`issues_article`.`id` = `uploads_image`.`object_id`) WHERE (`uploads_image`.`id` IS NOT NULL AND `uploads_image`.`content_type_id` = 26 ) LIMIT 21