Django에서 여러 filter () 연결, 이것이 버그입니까?

Django에서 여러 filter () 연결, 이것이 버그입니까?

---

저는 항상 Django에서 여러 filter () 호출을 연결하는 것이 단일 호출로 수집하는 것과 항상 동일하다고 가정했습니다.

# Equivalent
Model.objects.filter(foo=1).filter(bar=2)
Model.objects.filter(foo=1,bar=2)

하지만 나는 이것이 사실이 아닌 내 코드에서 복잡한 쿼리 세트를 실행했습니다.

class Inventory(models.Model):
    book = models.ForeignKey(Book)

class Profile(models.Model):
    user = models.OneToOneField(auth.models.User)
    vacation = models.BooleanField()
    country = models.CharField(max_length=30)

# Not Equivalent!
Book.objects.filter(inventory__user__profile__vacation=False).filter(inventory__user__profile__country='BR')
Book.objects.filter(inventory__user__profile__vacation=False, inventory__user__profile__country='BR')

생성 된 SQL은

SELECT "library_book"."id", "library_book"."asin", "library_book"."added", "library_book"."updated" FROM "library_book" INNER JOIN "library_inventory" ON ("library_book"."id" = "library_inventory"."book_id") INNER JOIN "auth_user" ON ("library_inventory"."user_id" = "auth_user"."id") INNER JOIN "library_profile" ON ("auth_user"."id" = "library_profile"."user_id") INNER JOIN "library_inventory" T5 ON ("library_book"."id" = T5."book_id") INNER JOIN "auth_user" T6 ON (T5."user_id" = T6."id") INNER JOIN "library_profile" T7 ON (T6."id" = T7."user_id") WHERE ("library_profile"."vacation" = False  AND T7."country" = BR )
SELECT "library_book"."id", "library_book"."asin", "library_book"."added", "library_book"."updated" FROM "library_book" INNER JOIN "library_inventory" ON ("library_book"."id" = "library_inventory"."book_id") INNER JOIN "auth_user" ON ("library_inventory"."user_id" = "auth_user"."id") INNER JOIN "library_profile" ON ("auth_user"."id" = "library_profile"."user_id") WHERE ("library_profile"."vacation" = False  AND "library_profile"."country" = BR )

연결 filter()호출 이있는 첫 번째 쿼리 세트 는 인벤토리 모델에 두 번 효과적으로 결합하여 두 조건 사이에 OR을 생성하는 반면 두 번째 쿼리 세트는 두 조건을 함께 AND로 만듭니다. 첫 번째 쿼리와 두 가지 조건이있을 것으로 예상했습니다. 이것이 예상되는 동작입니까, 아니면 Django의 버그입니까?

관련 질문에 대한 답변 Django에서 ".filter (). filter (). filter () ..."사용에 대한 단점이 있습니까? 두 쿼리 세트가 동일해야 함을 나타내는 것 같습니다.

---

I가 이해하는 방법은 설계 미묘하게 다르다 (및 I 확실히 보정 오픈이다)이다 : filter(A, B)동안 것 제 필터, B에 따라서 다음 서브 필터 (A)에있어서, 그리고 filter(A).filter(B)잠재적으로는 상이한 일치 '및'열을 반환 B와 일치하는 행.

여기에서 예를보십시오.

https://docs.djangoproject.com/en/dev/topics/db/queries/#spanning-multi-valued-relationships

특별히:

단일 filter () 호출 내의 모든 것이 동시에 적용되어 이러한 모든 요구 사항과 일치하는 항목을 필터링합니다. 연속적인 filter () 호출은 객체 세트를 더욱 제한합니다.

...

이 두 번째 예제 (filter (A) .filter (B))에서 첫 번째 필터는 쿼리 세트를 (A)로 제한했습니다. 두 번째 필터는 블로그 세트를 (B)로 제한했습니다. 두 번째 필터에서 선택한 항목은 첫 번째 필터의 항목과 같을 수도 있고 같지 않을 수도 있습니다 .`

---

이 두 스타일의 필터링은 대부분의 경우 동일하지만 ForeignKey 또는 ManyToManyField를 기반으로하는 개체에 대한 쿼리의 경우 약간 다릅니다.

문서의 예 .

모델
Blog to Entry는 일대 다 관계입니다.

from django.db import models

class Blog(models.Model):
    ...

class Entry(models.Model):
    blog = models.ForeignKey(Blog)
    headline = models.CharField(max_length=255)
    pub_date = models.DateField()
    ...

개체
여기에 블로그 및 항목 개체가 있다고 가정합니다.

쿼리

Blog.objects.filter(entry__headline_contains='Lennon', 
    entry__pub_date__year=2008)
Blog.objects.filter(entry__headline_contains='Lennon').filter(
    entry__pub_date__year=2008)  

첫 번째 쿼리 (단일 필터 1)의 경우 blog1 과만 일치합니다.

For the 2nd query (chained filters one), it filters out blog1 and blog2.
The first filter restricts the queryset to blog1, blog2 and blog5; the second filter restricts the set of blogs further to blog1 and blog2.

And you should realize that

We are filtering the Blog items with each filter statement, not the Entry items.

So, it's not the same, because Blog and Entry are multi-valued relationships.

Reference: https://docs.djangoproject.com/en/1.8/topics/db/queries/#spanning-multi-valued-relationships
If there is something wrong, please correct me.

Edit: Changed v1.6 to v1.8 since the 1.6 links are no longer available.

---

As you can see in the generated SQL statements the difference is not the "OR" as some may suspect. It is how the WHERE and JOIN is placed.

Example1 (same joined table) :

(example from https://docs.djangoproject.com/en/dev/topics/db/queries/#spanning-multi-valued-relationships)

Blog.objects.filter(entry__headline__contains='Lennon', entry__pub_date__year=2008)

This will give you all the Blogs that have one entry with both (entry_headline_contains='Lennon') AND (entry__pub_date__year=2008), which is what you would expect from this query. Result: Book with {entry.headline: 'Life of Lennon', entry.pub_date: '2008'}

Example 2 (chained)

Blog.objects.filter(entry__headline__contains='Lennon').filter(entry__pub_date__year=2008)

This will cover all the results from Example 1, but it will generate slightly more result. Because it first filters all the blogs with (entry_headline_contains='Lennon') and then from the result filters (entry__pub_date__year=2008).

The difference is that it will also give you results like: Book with {entry.headline: 'Lennon', entry.pub_date: 2000}, {entry.headline: 'Bill', entry.pub_date: 2008}

In your case

I think it is this one you need:

Book.objects.filter(inventory__user__profile__vacation=False, inventory__user__profile__country='BR')

And if you want to use OR please read: https://docs.djangoproject.com/en/dev/topics/db/queries/#complex-lookups-with-q-objects

---

Sometimes you don't want to join multiple filters together like this:

def your_dynamic_query_generator(self, event: Event):
    qs \
    .filter(shiftregistrations__event=event) \
    .filter(shiftregistrations__shifts=False)

And the following code would actually not return the correct thing.

def your_dynamic_query_generator(self, event: Event):
    return Q(shiftregistrations__event=event) & Q(shiftregistrations__shifts=False)

What you can do now is to use an annotation count-filter.

In this case we count all shifts which belongs to a certain event.

qs: EventQuerySet = qs.annotate(
    num_shifts=Count('shiftregistrations__shifts', filter=Q(shiftregistrations__event=event))
)

Afterwards you can filter by annotation.

def your_dynamic_query_generator(self):
    return Q(num_shifts=0)

This solution is also cheaper on large querysets.

Hope this helps.

참고 URL : https://stackoverflow.com/questions/8164675/chaining-multiple-filter-in-django-is-this-a-bug