def test_aggregate(self):
"""
filtered_relation() not only improves performance but also creates
correct results when aggregating with multiple LEFT JOINs.
Books can be reserved then rented by a borrower. Each reservation and
rental session are recorded with Reservation and RentalSession models.
Every time a reservation or a rental session is over, their state is
changed to 'stopped'.
Goal: Count number of books that are either currently reserved or
rented by borrower1 or available.
"""
qs = Book.objects.annotate(is_reserved_or_rented_by=Case(
When(reservation__state=Reservation.NEW,
then=F('reservation__borrower__pk')),
When(rental_session__state=RentalSession.NEW,
then=F('rental_session__borrower__pk')),
default=None,
)).filter(
Q(is_reserved_or_rented_by=self.borrower1.pk)
| Q(state=Book.AVAILABLE)).distinct()
self.assertEqual(qs.count(), 1)
# If count is equal to 1, the same aggregation should return in the
# same result but it returns 4.
self.assertSequenceEqual(
qs.annotate(total=Count('pk')).values('total'), [{
'total': 4
}])
# With FilteredRelation, the result is as expected (1).
qs = Book.objects.annotate(active_reservations=FilteredRelation(
'reservation',
condition=Q(
reservation__state=Reservation.NEW,
reservation__borrower=self.borrower1,
)), ).annotate(active_rental_sessions=FilteredRelation(
'rental_session',
condition=Q(
rental_session__state=RentalSession.NEW,
rental_session__borrower=self.borrower1,
)), ).filter((Q(active_reservations__isnull=False)
| Q(active_rental_sessions__isnull=False))
| Q(state=Book.AVAILABLE)).distinct()
self.assertEqual(qs.count(), 1)
self.assertSequenceEqual(
qs.annotate(total=Count('pk')).values('total'), [{
'total': 1
}])
def filter_related(self, name: str, *, include_all=False, **conditions) -> 'HQuerySet':
""" 利用 FilteredRelation 优化 Query 的方法
官方文档参见: https://docs.djangoproject.com/en/2.1/ref/models/querysets/#filteredrelation-objects
还有一种写法是 Manager.from_queryset, 不过那样就没有 Pycharm Django 的补全和提示了，很不好
https://docs.djangoproject.com/en/2.1/topics/db/managers/#calling-custom-queryset-methods-from-the-manager
Examples::
queryset = account.followers.filter(tags__name='rap', tags__deactivated_at__isnull=True)
Equals to::
queryset = account.followers.filter_related('tags', name='rap')
:param name: Django related name
:param include_all: True to include deactivated instances
:param conditions: real filters
"""
filtered_name = f'filtered_{name}'
key, value = conditions.popitem()
condition = {f'{filtered_name}__{key}': value}
if not include_all:
conditions.setdefault('deactivated_at__isnull', True)
conditions = {f'{name}__{k}': v for k, v in conditions.items()}
return self._queryset.annotate(**{filtered_name: FilteredRelation(name, condition=Q(**conditions))}) \
.filter(**condition)
def get(self, request, *args, **kwargs):
print(request.user)
try:
now = datetime.datetime.today()
queryset = Party.objects.prefetch_related('place')
if request.user.is_authenticated:
queryset = queryset.filter(
Q(start_datetime__gte=now)
| Q(end_datetime__gte=now)).annotate(
party_like1=FilteredRelation(
'party_like',
condition=Q(party_like__user_id=request.user.id)),
is_liked=Case(When(Q(party_like1__isnull=True),
then=False),
When(Q(party_like1__isnull=False),
then=F('party_like1__like')),
output_field=BooleanField()))
else:
queryset = queryset.filter(
Q(start_datetime__gte=now)
| Q(end_datetime__gte=now)).annotate(is_liked=Value(
False, BooleanField()), )
queryset = queryset.order_by('start_datetime', '-like_count')
page = self.paginate_queryset(queryset)
if page is not None:
serializer = self.serializer_class(page, many=True)
return self.get_paginated_response(serializer.data)
except Party.DoesNotExist as e:
raise Http404
def _entry_profile(self) -> None:
in_query = self.base_query.values('qa__id')
results = (QualificationAim.objects.filter(
id__in=Subquery(in_query)).annotate(
default_address=FilteredRelation(
'student__address',
condition=Q(student__address__is_default=True)),
postcode=F('default_address__postcode'),
).select_related(
'student__domicile',
'student',
).order_by('id').distinct())
for row in results:
models.EntryProfile.objects.create(
batch=self.batch,
instanceid_fk=self._instance_id(row.id),
domicile=row.student.domicile.hesa_code,
qualent3=row.entry_qualification_id,
postcode=_correct_postcode(row.student.termtime_postcode
or row.postcode or '')
if row.student.domicile.hesa_code in ('XF', 'XG', 'XH', 'XI',
'XK', 'XL', 'GG', 'JE',
'IM') else None,
)
def annotate_dataset(queryset, c_filter, bbl_values):
model_name = list(
filter(lambda x: c_filter['model'].lower() == x.lower(),
settings.ACTIVE_MODELS))
model = getattr(ds, model_name[0])
queryset = queryset.annotate(
**{
c_filter['related_annotation_key']:
FilteredRelation(c_filter['model'],
condition=Q(
construct_and_q(c_filter['query1_filters'])))
})
if c_filter['count_annotation_key']:
serializer_count_key = model_name[0].lower() + 's__count'
queryset = queryset.annotate(
**{
c_filter['count_annotation_key']:
Count(c_filter['related_annotation_key'], distinct=True)
})
# double annotate with a normal key ie: filter_0_hpdviolations__count becomes hpdviolations__count
queryset = queryset.annotate(
**{
serializer_count_key:
Count(c_filter['related_annotation_key'], distinct=True)
})
return queryset
def test_nested_m2m_filtered(self):
qs = Book.objects.annotate(favorite_book=FilteredRelation(
'author__favorite_books',
condition=Q(
author__favorite_books__title__icontains='book by')), ).values(
'title',
'favorite_book__pk',
).order_by('title', 'favorite_book__title')
self.assertSequenceEqual(qs, [
{
'title': self.book1.title,
'favorite_book__pk': self.book2.pk
},
{
'title': self.book1.title,
'favorite_book__pk': self.book3.pk
},
{
'title': self.book4.title,
'favorite_book__pk': self.book2.pk
},
{
'title': self.book4.title,
'favorite_book__pk': self.book3.pk
},
{
'title': self.book2.title,
'favorite_book__pk': None
},
{
'title': self.book3.title,
'favorite_book__pk': None
},
])
def _assignable_people(self, user):
return (
Person.objects.get_queryset().filter(is_vol_prospect=True).filter(
suppressed_at__isnull=True)
# Exclude any person already assigned to this user.
.annotate(my_assignments=FilteredRelation(
"vol_prospect_assignments",
condition=Q(vol_prospect_assignments__user=user),
)).filter(my_assignments=None)
# Exclude any person with a live (not suppressed, not expired) assignment.
.annotate(live_assignments=FilteredRelation(
"vol_prospect_assignments",
condition=Q(
vol_prospect_assignments__suppressed_at__isnull=True)
& Q(vol_prospect_assignments__expired_at__isnull=True),
)).filter(live_assignments=None))
def test_deep_nested_foreign_key(self):
qs = (
Book.objects.annotate(
author_favorite_book_editor=FilteredRelation(
"author__favorite_books__editor",
condition=Q(author__favorite_books__title__icontains="Jane A"),
),
)
.filter(
author_favorite_book_editor__isnull=False,
)
.select_related(
"author_favorite_book_editor",
)
.order_by("pk", "author_favorite_book_editor__pk")
)
with self.assertNumQueries(1):
self.assertQuerysetEqual(
qs,
[
(self.book1, self.editor_b),
(self.book4, self.editor_b),
],
lambda x: (x, x.author_favorite_book_editor),
)
def test_nested_m2m_filtered(self):
qs = (
Book.objects.annotate(
favorite_book=FilteredRelation(
"author__favorite_books",
condition=Q(author__favorite_books__title__icontains="book by"),
),
)
.values(
"title",
"favorite_book__pk",
)
.order_by("title", "favorite_book__title")
)
self.assertSequenceEqual(
qs,
[
{"title": self.book1.title, "favorite_book__pk": self.book2.pk},
{"title": self.book1.title, "favorite_book__pk": self.book3.pk},
{"title": self.book4.title, "favorite_book__pk": self.book2.pk},
{"title": self.book4.title, "favorite_book__pk": self.book3.pk},
{"title": self.book2.title, "favorite_book__pk": None},
{"title": self.book3.title, "favorite_book__pk": None},
],
)
def get_product(self, product_id):
if self.is_user():
filtered_user = self.parent
else:
filtered_user = self
products = Product.objects.annotate(by_user=FilteredRelation('productuserrel', condition = Q(productuserrel__user=filtered_user))).filter(Q(by_user__isnull = True) | Q(by_user__user=filtered_user)).filter(product_is_active=True).filter(product_id=product_id).values_list('product_id', 'product_name', 'product_cost', 'by_user__cost', named=True).order_by('product_name')
return products[0] if len(products) > 0 else None
def test_as_subquery(self):
inner_qs = Author.objects.annotate(book_alice=FilteredRelation(
'book',
condition=Q(book__title__iexact='poem by alice')), ).filter(
book_alice__isnull=False)
qs = Author.objects.filter(id__in=inner_qs)
self.assertSequenceEqual(qs, [self.author1])
def test_reverse_related_validation_with_filtered_relation(self):
fields = 'userprofile, userstat, relation'
with self.assertRaisesMessage(FieldError,
self.invalid_error % ('foobar', fields)):
list(
User.objects.annotate(relation=FilteredRelation(
'userprofile')).select_related('foobar'))
def test_with_m2m(self):
qs = Author.objects.annotate(
favorite_books_written_by_jane=FilteredRelation(
'favorite_books', condition=Q(favorite_books__in=[self.book2]),
),
).filter(favorite_books_written_by_jane__isnull=False)
self.assertSequenceEqual(qs, [self.author1])
def test_nested_foreign_key(self):
qs = (
Author.objects.annotate(
book_editor_worked_with=FilteredRelation(
"book__editor",
condition=Q(book__title__icontains="book by"),
),
)
.filter(
book_editor_worked_with__isnull=False,
)
.select_related(
"book_editor_worked_with",
)
.order_by("pk", "book_editor_worked_with__pk")
)
with self.assertNumQueries(1):
self.assertQuerysetEqual(
qs,
[
(self.author1, self.editor_a),
(self.author2, self.editor_b),
(self.author2, self.editor_b),
],
lambda x: (x, x.book_editor_worked_with),
)
def test_with_condition_as_expression_error(self):
msg = 'condition argument must be a Q() instance.'
expression = Case(
When(book__title__iexact='poem by alice', then=True), default=False,
)
with self.assertRaisesMessage(ValueError, msg):
FilteredRelation('book', condition=expression)
def test_with_join(self):
self.assertSequenceEqual(
Author.objects.annotate(
book_alice=FilteredRelation('book', condition=Q(book__title__iexact='poem by alice')),
).filter(book_alice__isnull=False),
[self.author1]
)
def test_values(self):
self.assertSequenceEqual(
Author.objects.annotate(
book_alice=FilteredRelation('book', condition=Q(book__title__iexact='poem by alice')),
).filter(book_alice__isnull=False).values(),
[{'id': self.author1.pk, 'name': 'Alice', 'content_type_id': None, 'object_id': None}]
)
def test_select_for_update(self):
self.assertSequenceEqual(
Author.objects.annotate(
book_jane=FilteredRelation('book', condition=Q(book__title__iexact='the book by jane a')),
).filter(book_jane__isnull=False).select_for_update(),
[self.author2]
)
def test_with_m2m_multijoin(self):
qs = Author.objects.annotate(
favorite_books_written_by_jane=FilteredRelation(
'favorite_books', condition=Q(favorite_books__author=self.author2),
)
).filter(favorite_books_written_by_jane__editor__name='b').distinct()
self.assertSequenceEqual(qs, [self.author1])
def test_values_list(self):
self.assertSequenceEqual(
Author.objects.annotate(
book_alice=FilteredRelation('book', condition=Q(book__title__iexact='poem by alice')),
).filter(book_alice__isnull=False).values_list('book_alice__title', flat=True),
['Poem by Alice']
)
def test_multiple_times(self):
self.assertSequenceEqual(
Author.objects.annotate(
book_title_alice=FilteredRelation('book', condition=Q(book__title__icontains='alice')),
).filter(book_title_alice__isnull=False).filter(book_title_alice__isnull=False).distinct(),
[self.author1]
)
def test_with_m2m_deep(self):
qs = Author.objects.annotate(
favorite_books_written_by_jane=FilteredRelation(
'favorite_books', condition=Q(favorite_books__author=self.author2),
),
).filter(favorite_books_written_by_jane__title='The book by Jane B')
self.assertSequenceEqual(qs, [self.author1])
def test_exclude_relation_with_join(self):
self.assertSequenceEqual(
Author.objects.annotate(
book_alice=FilteredRelation('book', condition=~Q(book__title__icontains='alice')),
).filter(book_alice__isnull=False).distinct(),
[self.author2]
)
def test_with_multiple_filter(self):
self.assertSequenceEqual(
Author.objects.annotate(book_editor_a=FilteredRelation(
'book',
condition=Q(book__title__icontains='book',
book__editor_id=self.editor_a.pk),
), ).filter(book_editor_a__isnull=False), [self.author1])
def test_with_foreign_key_on_condition_error(self):
msg = ("FilteredRelation's condition doesn't support nested relations "
"(got 'book__editor__name__icontains').")
with self.assertRaisesMessage(ValueError, msg):
list(
Author.objects.annotate(book_edited_by_b=FilteredRelation(
'book', condition=Q(book__editor__name__icontains='b')), ))
def test_nested_foreign_key_nested_field(self):
qs = (
Author.objects.annotate(
book_editor_worked_with=FilteredRelation(
"book__editor", condition=Q(book__title__icontains="book by")
),
)
.filter(
book_editor_worked_with__isnull=False,
)
.values(
"name",
"book_editor_worked_with__name",
)
.order_by("name", "book_editor_worked_with__name")
.distinct()
)
self.assertSequenceEqual(
qs,
[
{
"name": self.author1.name,
"book_editor_worked_with__name": self.editor_a.name,
},
{
"name": self.author2.name,
"book_editor_worked_with__name": self.editor_b.name,
},
],
)
def test_relation_name_lookup(self):
msg = ("FilteredRelation's relation_name cannot contain lookups (got "
"'book__title__icontains').")
with self.assertRaisesMessage(ValueError, msg):
Author.objects.annotate(book_title=FilteredRelation(
"book__title__icontains",
condition=Q(book__title="Poem by Alice"),
), )
def test_with_generic_foreign_key(self):
self.assertSequenceEqual(
Book.objects.annotate(generic_authored_book=FilteredRelation(
"generic_author",
condition=Q(generic_author__isnull=False)), ).filter(
generic_authored_book__isnull=False),
[self.book1],
)
def test_internal_queryset_alias_mapping(self):
queryset = Author.objects.annotate(
book_alice=FilteredRelation('book', condition=Q(book__title__iexact='poem by alice')),
).filter(book_alice__isnull=False)
self.assertIn(
'INNER JOIN {} book_alice ON'.format(connection.ops.quote_name('filtered_relation_book')),
str(queryset.query)
)
def test_only_not_supported(self):
msg = 'only() is not supported with FilteredRelation.'
with self.assertRaisesMessage(ValueError, msg):
Author.objects.annotate(book_alice=FilteredRelation(
'book',
condition=Q(book__title__iexact='poem by alice')), ).filter(
book_alice__isnull=False).select_related(
'book_alice').only('book_alice__state')