def test_defer(self):
# One query for the list and one query for the deferred title.
with self.assertNumQueries(2):
self.assertQuerysetEqual(
Author.objects.annotate(book_alice=FilteredRelation(
'book', condition=Q(
book__title__iexact='poem by alice')), ).filter(
book_alice__isnull=False).select_related(
'book_alice').defer('book_alice__title'),
['Poem by Alice'], lambda author: author.book_alice.title)
def tag_ids(user_obj, tag_list: list):
"""通过标签id查询菜品"""
for tag_id in tag_list:
DishQueryFunctionSet.add_search_item(user_obj, "tag", str(tag_id))
return Dish.objects.prefetch_related('tag').filter(
tag__in=tag_list).annotate(t=FilteredRelation(
'likedish', condition=Q(likedish__user=user_obj))).annotate(
user_like=F('t__like')).annotate(
user_dislike=1 - F('t__like')).order_by('id')
def test_with_join_and_complex_condition(self):
self.assertSequenceEqual(
Author.objects.annotate(book_alice=FilteredRelation(
"book",
condition=Q(
Q(book__title__iexact="poem by alice")
| Q(book__state=Book.RENTED)),
), ).filter(book_alice__isnull=False),
[self.author1],
)
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 _assign_to_unverified_user(self, user, num):
demo_people = (Person.objects.get_demo_queryset().filter(
is_vol_prospect=True, suppressed_at__isnull=True).annotate(
my_assignments=FilteredRelation(
"vol_prospect_assignments",
condition=Q(vol_prospect_assignments__user=user),
)).filter(my_assignments=None)[:num])
return [
self.create(user=user, person=assignee) for assignee in demo_people
]
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_select_related_multiple(self):
qs = (
Book.objects.annotate(
author_join=FilteredRelation("author"),
editor_join=FilteredRelation("editor"),
)
.select_related("author_join", "editor_join")
.order_by("pk")
)
self.assertQuerysetEqual(
qs,
[
(self.book1, self.author1, self.editor_a),
(self.book2, self.author2, self.editor_b),
(self.book3, self.author2, self.editor_b),
(self.book4, self.author1, self.editor_a),
],
lambda x: (x, x.author_join, x.editor_join),
)
def test_aggregate(self):
tests = [
Q(daily_sales__sale_date__gte=self.sales_date2),
~Q(daily_sales__seller=self.seller1),
]
for condition in tests:
with self.subTest(condition=condition):
qs = (
Book.objects.annotate(
recent_sales=FilteredRelation(
"daily_sales", condition=condition
),
recent_sales_rates=FilteredRelation(
"recent_sales__currency__rates_from",
condition=Q(
recent_sales__currency__rates_from__rate_date=F(
"recent_sales__sale_date"
),
recent_sales__currency__rates_from__to_currency=(
self.usd
),
),
),
)
.annotate(
sales_sum=Sum(
F("recent_sales__sales") * F("recent_sales_rates__rate"),
output_field=DecimalField(),
),
)
.values("title", "sales_sum")
.order_by(
F("sales_sum").desc(nulls_last=True),
)
)
self.assertSequenceEqual(
qs,
[
{"title": self.book2.title, "sales_sum": Decimal(150.00)},
{"title": self.book1.title, "sales_sum": Decimal(50.00)},
{"title": self.book3.title, "sales_sum": None},
],
)
def test_select_related_foreign_key(self):
qs = Book.objects.annotate(author_join=FilteredRelation(
'author'), ).select_related('author_join').order_by('pk')
with self.assertNumQueries(1):
self.assertQuerysetEqual(qs, [
(self.book1, self.author1),
(self.book2, self.author2),
(self.book3, self.author2),
(self.book4, self.author1),
], lambda x: (x, x.author_join))
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_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"
)
def _annotate_has_access(datasets, user):
"""
Adds a bool annotation to queryset if user has access to the dataset
@param datasets: django queryset
@param user: request.user
@return: queryset
"""
if datasets.model is ReferenceDataset:
datasets = datasets.annotate(has_access=Value(True, BooleanField()))
if datasets.model is DataSet or datasets.model is VisualisationCatalogueItem:
if datasets.model is DataSet:
datasets = datasets.annotate(user_permission=FilteredRelation(
"datasetuserpermission",
condition=Q(datasetuserpermission__user=user),
), )
if datasets.model is VisualisationCatalogueItem:
datasets = datasets.annotate(user_permission=FilteredRelation(
"visualisationuserpermission",
condition=Q(visualisationuserpermission__user=user),
), )
datasets = datasets.annotate(has_access=BoolOr(
Case(
When(
Q(user_access_type__in=[
UserAccessType.REQUIRES_AUTHENTICATION,
UserAccessType.OPEN,
])
| (Q(
user_access_type=UserAccessType.REQUIRES_AUTHORIZATION,
user_permission__user__isnull=False,
))
| Q(authorized_email_domains__contains=[
user.email.split("@")[1]
]),
then=True,
),
default=False,
output_field=BooleanField(),
)), )
return datasets
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_prefetch_related(self):
msg = 'prefetch_related() is not supported with FilteredRelation.'
qs = Author.objects.annotate(
book_title_contains_b=FilteredRelation('book', condition=Q(book__title__icontains='b')),
).filter(
book_title_contains_b__isnull=False,
)
with self.assertRaisesMessage(ValueError, msg):
qs.prefetch_related('book_title_contains_b')
with self.assertRaisesMessage(ValueError, msg):
qs.prefetch_related('book_title_contains_b__editor')
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_extra(self):
self.assertSequenceEqual(
Author.objects.annotate(
book_alice=FilteredRelation(
"book", condition=Q(book__title__iexact="poem by alice")
),
)
.filter(book_alice__isnull=False)
.extra(where=["1 = 1"]),
[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_select_related(self):
qs = Author.objects.annotate(
book_join=FilteredRelation('book'),
).select_related('book_join__editor').order_by('pk', 'book_join__pk')
with self.assertNumQueries(1):
self.assertQuerysetEqual(qs, [
(self.author1, self.book1, self.editor_a, self.author1),
(self.author1, self.book4, self.editor_a, self.author1),
(self.author2, self.book2, self.editor_b, self.author2),
(self.author2, self.book3, self.editor_b, self.author2),
], lambda x: (x, x.book_join, x.book_join.editor, x.book_join.author))
def get_queryset(self):
langs = get_active_language_choices()
lang1 = langs[0]
lang2 = langs[1] if len(langs) > 1 else "nonexistent"
return (
super()
.get_queryset()
.annotate(
translation1=FilteredRelation(
"translations", condition=Q(translations__language_code=lang1)
)
)
.annotate(
translation2=FilteredRelation(
"translations", condition=Q(translations__language_code=lang2)
)
)
.annotate(descr=Coalesce("translation1__descr", "translation2__descr"))
.order_by("descr")
)
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_with_foreign_key_error(self):
msg = (
"FilteredRelation's condition doesn't support nested relations "
"(got 'author__favorite_books__author')."
)
with self.assertRaisesMessage(ValueError, msg):
list(Book.objects.annotate(
alice_favorite_books=FilteredRelation(
'author__favorite_books',
condition=Q(author__favorite_books__author=self.author1),
)
))
def get_success_json_response(self, obj):
num_in_cart = OrderItem.objects.annotate(user_orders=FilteredRelation(
'order',
condition=Q(order__user_id=self.request.user.id),
), ).filter(user_orders__status=Order.CART).count()
context = {
'num_in_cart':
num_in_cart,
"success": ("The quantity of the item has changed."
if obj.quantity else "Item removed from cart."),
}
return JsonResponse(context, status=200)
def get_queryset(self):
queryset = super().get_queryset()
if self.request.GET.get("o") in ["author", "-author"]:
queryset = queryset.annotate(
main=FilteredRelation(
"bookauthorspriority", condition=Q(bookauthorspriority__priority=1)
)
)
self.filterset = self.filterset_class(self.request.GET, queryset=queryset)
return self.filterset.qs
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_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_condition_outside_relation_name(self):
msg = (
"FilteredRelation's condition doesn't support relations outside "
"the 'book__editor' (got 'book__author__name__icontains')."
)
with self.assertRaisesMessage(ValueError, msg):
Author.objects.annotate(
book_editor=FilteredRelation(
"book__editor",
condition=Q(book__author__name__icontains="book"),
),
)
def test_nested_chained_relations(self):
qs = Author.objects.annotate(
my_books=FilteredRelation(
'book',
condition=Q(book__title__icontains='book by'),
),
preferred_by_authors=FilteredRelation(
'my_books__preferred_by_authors',
condition=Q(my_books__preferred_by_authors__name='Alice'),
),
).annotate(
author=F('name'),
book_title=F('my_books__title'),
preferred_by_author_pk=F('preferred_by_authors'),
).order_by('author', 'book_title', 'preferred_by_author_pk')
self.assertQuerysetEqual(
qs, [
('Alice', 'The book by Alice', None),
('Jane', 'The book by Jane A', self.author1.pk),
('Jane', 'The book by Jane B', self.author1.pk),
], lambda x: (x.author, x.book_title, x.preferred_by_author_pk))
def sort_by_payment(queryset: QuerySet, sort_by: SortInputObjectType) -> QuerySet:
last_payments = (
queryset.exclude(payments__isnull=True)
.annotate(payment_id=Max("payments__pk"))
.values_list("payment_id", flat=True)
)
queryset = queryset.annotate(
last_payment=FilteredRelation(
"payments", condition=Q(payments__pk__in=last_payments)
)
)
return queryset.order_by(f"{sort_by.direction}last_payment__charge_status")
def get_annotated_fractionflows(keyflow_id, strategy_id=None):
'''
returns fraction flows in given keyflow
annotates fraction flow queryset flows with values of fields
of strategy fraction flows ('strategy_' as prefix to original field)
strategy fraction flows override fields of fraction flow (prefix 'strategy_') if
changed in strategy
'''
queryset = FractionFlow.objects
if not strategy_id:
queryset = queryset.filter(
keyflow__id=keyflow_id,
strategy__isnull=True).\
annotate(
strategy_amount=F('amount'),
strategy_material=F('material'),
strategy_material_name=F('material__name'),
strategy_material_level=F('material__level'),
strategy_waste=F('waste'),
strategy_hazardous=F('hazardous'),
strategy_process=F('process'),
# just setting Value(0) doesn't seem to work
strategy_delta=F('strategy_amount') - F('amount')
)
else:
qs1 = queryset.filter(
Q(keyflow__id=keyflow_id) &
(Q(strategy__isnull=True) |
Q(strategy_id=strategy_id))
)
qsfiltered = qs1.annotate(sf=FilteredRelation(
'f_strategyfractionflow',
condition=Q(f_strategyfractionflow__strategy=strategy_id)))
queryset = qsfiltered.annotate(
# strategy fraction flow overrides amounts
strategy_amount=Coalesce('sf__amount', 'amount'),
strategy_material=Coalesce('sf__material', 'material'),
strategy_material_name=Coalesce(
'sf__material__name', 'material__name'),
strategy_material_level=Coalesce(
'sf__material__level', 'material__level'),
strategy_waste=Coalesce('sf__waste', 'waste'),
strategy_hazardous=Coalesce('sf__hazardous', 'hazardous'),
strategy_process=Coalesce('sf__process', 'process'),
#strategy_delta=Case(When(strategy=strategy,
#then=F('strategy_amount')),
#default=F('strategy_amount') - F('amount'))
)
return queryset.order_by('origin', 'destination')
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],
)
