对Django查询强制执行INNER JOIN [英] Force INNER JOIN for Django Query

问题描述

这是我的模式:

城市->摄影师

我正在尝试获取至少有一名摄影师的城市列表，并返回该城市的摄影师人数.

I'm trying to get a list of cities that have at least one photographer, and return the photographer count for the cities.

这是我正在使用的查询集:

Here is the queryset I'm working with:

City.objects.annotate(photographer_count=aggregates.Count('photographers')).filter(photographer_count__gt=0).order_by('-photographer_count')

这完全符合我的预期，但出于某些原因，Django选择使用左外部连接在城市/摄影师之间进行连接.如果我抓取SQL文本并简单地将"left external"更改为"inner"，则查询从〜11秒变为200ms，结果相同.

This works exactly as I would expect it too, except for some reason Django chooses to make the join between city/photographer with a left outer join. If I grab the SQL text and simply change the "left outer" to "inner", the query goes from ~11 seconds to 200ms with identical results.

我尝试在注释的前面放置一个过滤器，以提示Django它应该是内部连接，但这是行不通的.

I've tried putting a filter in front of the annotate to hint to Django that it should be inner joining, but that didn't work.

任何Django查询voodoo我都可以对此执行以获取内部联接?我意识到我可以使用简单的SQL，但是更喜欢通过ORM.

Any Django query voodoo I can perform on this to get that inner join? I realize I can use straight SQL, but would prefer to go through the ORM.

推荐答案

默认情况下，会生成一个LEFT JOIN，以便Django即使在摄影师为零的城市也能为您排行.如果您知道您不想要这些，这是一个迫使Django生成INNER JOIN的技巧:

By default, a LEFT JOIN is generated so that Django can get you rows even for cities with zero photographers. If you know you don't want those, here's a trick to force Django to generate an INNER JOIN:

City.objects.filter(
    photographer__isnull=False
).annotate(
    photographer_count=aggregates.Count('photographers')
).filter(
    photographer_count__gt=0
).order_by(
    '-photographer_count'
)

具体来说，第一个过滤器告诉Django INNER JOIN是安全的. 必须出现在annotate()调用之前.

Specifically, that first filter tells Django that an INNER JOIN is safe. It must come before the annotate() call.

这篇关于对Django查询强制执行INNER JOIN的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！