def __new__(cls, field, *path):
if not path:
raise ValueError("Path must contain at least one key.")
head, *tail = path
field = KeyTextTransform(head, field)
for head in tail:
field = KeyTextTransform(head, field)
return field
def test_string_agg_jsonfield_ordering(self):
values = AggregateTestModel.objects.aggregate(stringagg=StringAgg(
KeyTextTransform('lang', 'json_field'),
delimiter=';',
ordering=KeyTextTransform('lang', 'json_field'),
output_field=CharField(),
), )
self.assertEqual(values, {'stringagg': 'en;pl'})
def test_string_agg_jsonfield_ordering(self):
values = AggregateTestModel.objects.aggregate(
stringagg=StringAgg(
KeyTextTransform("lang", "json_field"),
delimiter=";",
ordering=KeyTextTransform("lang", "json_field"),
output_field=CharField(),
),
)
self.assertEqual(values, {"stringagg": "en;pl"})
def test_lookups_with_key_transform(self):
tests = (
('value__d__contains', 'e'),
('value__baz__has_key', 'c'),
('value__baz__has_keys', ['a', 'c']),
('value__baz__has_any_keys', ['a', 'x']),
('value__contains', KeyTransform('bax', 'value')),
('value__has_key', KeyTextTransform('foo', 'value')),
)
# contained_by lookup is not supported on Oracle.
if connection.vendor != 'oracle':
tests += (
('value__baz__contained_by', {
'a': 'b',
'c': 'd',
'e': 'f'
}),
(
'value__contained_by',
KeyTransform(
'x',
RawSQL(
self.raw_sql,
['{"x": {"a": "b", "c": 1, "d": "e"}}'],
)),
),
)
for lookup, value in tests:
with self.subTest(lookup=lookup):
self.assertIs(
NullableJSONModel.objects.filter(**{
lookup: value
}, ).exists(), True)
def test_key_transform(self):
Detail.objects.bulk_create([
Detail(value={'department': 'IT', 'name': 'Smith', 'salary': 37000}),
Detail(value={'department': 'IT', 'name': 'Nowak', 'salary': 32000}),
Detail(value={'department': 'HR', 'name': 'Brown', 'salary': 50000}),
Detail(value={'department': 'HR', 'name': 'Smith', 'salary': 55000}),
Detail(value={'department': 'PR', 'name': 'Moore', 'salary': 90000}),
])
qs = Detail.objects.annotate(department_sum=Window(
expression=Sum(Cast(
KeyTextTransform('salary', 'value'),
output_field=IntegerField(),
)),
partition_by=[KeyTransform('department', 'value')],
order_by=[KeyTransform('name', 'value')],
)).order_by('value__department', 'department_sum')
self.assertQuerysetEqual(qs, [
('Brown', 'HR', 50000, 50000),
('Smith', 'HR', 55000, 105000),
('Nowak', 'IT', 32000, 32000),
('Smith', 'IT', 37000, 69000),
('Moore', 'PR', 90000, 90000),
], lambda entry: (
entry.value['name'],
entry.value['department'],
entry.value['salary'],
entry.department_sum,
))
def top_per_user(self, request: Request):
"""Get the top_n events grouped by user count"""
filtered_action = request.query_params.get("action", EventAction.LOGIN)
top_n = request.query_params.get("top_n", 15)
return Response(
get_objects_for_user(request.user, "authentik_events.view_event")
.filter(action=filtered_action)
.exclude(context__authorized_application=None)
.annotate(application=KeyTextTransform("authorized_application", "context"))
.annotate(user_pk=KeyTextTransform("pk", "user"))
.values("application")
.annotate(counted_events=Count("application"))
.annotate(unique_users=Count("user_pk", distinct=True))
.values("unique_users", "application", "counted_events")
.order_by("-counted_events")[:top_n]
)
def filter(self, qs, value):
if value in EMPTY_VALUES:
return qs
for expr in value:
if expr in EMPTY_VALUES: # pragma: no cover
continue
lookup = expr.get("lookup") or self.lookup_expr
lookup_expr = (hasattr(lookup, "value") and lookup.value) or lookup
# "contains" behaves differently on JSONFields as it does on TextFields.
# That's why we annotate the queryset with the value.
# Some discussion about it can be found here:
# https://code.djangoproject.com/ticket/26511
if isinstance(expr["value"], str):
qs = qs.annotate(
field_val=Cast(
KeyTextTransform(expr["key"], self.field_name),
models.CharField(),
),
)
lookup = {f"field_val__{lookup_expr}": expr["value"]}
else:
lookup = {
f"{self.field_name}__{expr['key']}__{lookup_expr}": expr["value"]
}
qs = qs.filter(**lookup)
return qs
def populate_revision_content_type(apps, schema_editor):
ContentType = apps.get_model("contenttypes.ContentType")
Revision = apps.get_model("wagtailcore.Revision")
page_type = ContentType.objects.get(app_label="wagtailcore", model="page")
Revision.objects.all().update(
base_content_type=page_type,
content_type_id=Cast(
KeyTextTransform("content_type", models.F("content")),
output_field=models.PositiveIntegerField(),
),
)
def test_lookups_with_key_transform(self):
tests = (
('value__baz__has_key', 'c'),
('value__baz__has_keys', ['a', 'c']),
('value__baz__has_any_keys', ['a', 'x']),
('value__has_key', KeyTextTransform('foo', 'value')),
)
for lookup, value in tests:
with self.subTest(lookup=lookup):
self.assertIs(NullableJSONModel.objects.filter(
**{lookup: value},
).exists(), True)
def price_history(self, start_date: datetime.datetime, end_date: Optional[datetime.datetime] = datetime.datetime.now(),
time_period: Optional[str] = DAILY, aggregation: Optional[str] = OPERATOR_MEAN, **kwargs) -> DataFrame:
assert time_period in PRICE_TIME_PERIODS_LIST, f"time period must be one of {PRICE_TIME_PERIODS_LIST}"
assert aggregation in OPERATORS, f"operator must be one of {OPERATORS}"
price_history = self.websiteproductattributes.published()\
.filter(created__range=[start_date, end_date], attribute_type__name="price")\
.annotate(price=KeyTextTransform('value', 'data'))
if kwargs:
price_history = price_history.filter(**kwargs)
df: DataFrame = pd.DataFrame(price_history.values('created', 'price'))
if df.empty:
return df
df_grouper: Series = df['created'].dt.isocalendar().week if time_period == WEEKLY else getattr(df['created'].dt, time_period)
return getattr(df.groupby(by=df_grouper), aggregation)()
def test_expressions_with_key_transform(self):
constraint_name = 'exclude_overlapping_reservations_smoking'
constraint = ExclusionConstraint(
name=constraint_name,
expressions=[
(F('datespan'), RangeOperators.OVERLAPS),
(KeyTextTransform('smoking', 'requirements'), RangeOperators.EQUAL),
],
)
with connection.schema_editor() as editor:
editor.add_constraint(HotelReservation, constraint)
self.assertIn(
constraint_name,
self.get_constraints(HotelReservation._meta.db_table),
)
def test_ordering_grouping_by_key_transform(self):
base_qs = NullableJSONModel.objects.filter(value__d__0__isnull=False)
for qs in (
base_qs.order_by('value__d__0'),
base_qs.annotate(key=KeyTransform('0', KeyTransform('d', 'value'))).order_by('key'),
):
self.assertSequenceEqual(qs, [self.objs[4]])
qs = NullableJSONModel.objects.filter(value__isnull=False)
self.assertQuerysetEqual(
qs.filter(value__isnull=False).annotate(
key=KeyTextTransform('f', KeyTransform('1', KeyTransform('d', 'value'))),
).values('key').annotate(count=Count('key')).order_by('count'),
[(None, 0), ('g', 1)],
operator.itemgetter('key', 'count'),
)
def test_lookups_with_key_transform(self):
tests = (
("value__baz__has_key", "c"),
("value__baz__has_keys", ["a", "c"]),
("value__baz__has_any_keys", ["a", "x"]),
("value__has_key", KeyTextTransform("foo", "value")),
)
for lookup, value in tests:
with self.subTest(lookup=lookup):
self.assertIs(
NullableJSONModel.objects.filter(
**{lookup: value},
).exists(),
True,
)
def videos_with_truncatable_text(website: Website) -> List[WebsiteContent]:
"""Return a list of WebsiteContent objects with text fields that will be truncated in YouTube"""
if not is_ocw_site(website):
return []
query_resource_type_field = get_dict_query_field(
"metadata", settings.FIELD_RESOURCETYPE)
return (WebsiteContent.objects.annotate(desc_len=Length(
Cast(
KeyTextTransform(settings.YT_FIELD_DESCRIPTION, "metadata"),
CharField(),
))).annotate(title_len=Length("title")).filter(
Q(website=website)
& Q(**{query_resource_type_field: RESOURCE_TYPE_VIDEO})
& (Q(desc_len__gt=YT_MAX_LENGTH_DESCRIPTION)
| Q(title_len__gt=YT_MAX_LENGTH_TITLE))))
def test_ordering_grouping_by_key_transform(self):
base_qs = NullableJSONModel.objects.filter(value__d__0__isnull=False)
for qs in (
base_qs.order_by("value__d__0"),
base_qs.annotate(key=KeyTransform(
"0", KeyTransform("d", "value"))).order_by("key"),
):
self.assertSequenceEqual(qs, [self.objs[4]])
qs = NullableJSONModel.objects.filter(value__isnull=False)
self.assertQuerysetEqual(
qs.filter(value__isnull=False).annotate(key=KeyTextTransform(
"f", KeyTransform("1", KeyTransform(
"d", "value"))), ).values("key").annotate(
count=Count("key")).order_by("count"),
[(None, 0), ("g", 1)],
operator.itemgetter("key", "count"),
)
def resolve_expression(self,
query=None,
allow_joins=True,
reuse=None,
summarize=False,
for_save=False):
rhs = super().resolve_expression(query, allow_joins, reuse, summarize,
for_save)
if isinstance(rhs.field, TranslatedField):
field_list = self.name.split("__")
# TODO: should this always lookup lang
if len(field_list) == 1:
# Lookup current lang for one field
field_list.extend([get_current_language()])
for name in field_list[1:]:
# Perform key lookups along path
rhs = KeyTextTransform(name, rhs)
return rhs
def filter(self, qs, value):
if value in EMPTY_VALUES:
return qs
valid_lookups = qs.model._meta.get_field(self.field_name).get_lookups()
try:
value = json.loads(value)
except json.decoder.JSONDecodeError:
raise ValidationError("JSONValueFilter value needs to be json encoded.")
for expr in value:
if expr in EMPTY_VALUES: # pragma: no cover
continue
if not all(("key" in expr, "value" in expr)):
raise ValidationError(
'JSONValueFilter value needs to have a "key" and "value" and an '
'optional "lookup" key.'
)
lookup_expr = expr.get("lookup", self.lookup_expr)
if lookup_expr not in valid_lookups:
raise ValidationError(
f'Lookup expression "{lookup_expr}" not allowed for field '
f'"{self.field_name}". Valid expressions: '
f'{", ".join(valid_lookups.keys())}'
)
# "contains" behaves differently on JSONFields as it does on TextFields.
# That's why we annotate the queryset with the value.
# Some discussion about it can be found here:
# https://code.djangoproject.com/ticket/26511
if isinstance(expr["value"], str):
qs = qs.annotate(
field_val=Cast(
KeyTextTransform(expr["key"], self.field_name), CharField()
)
)
lookup = {f"field_val__{lookup_expr}": expr["value"]}
else:
lookup = {
f"{self.field_name}__{expr['key']}__{lookup_expr}": expr["value"]
}
qs = qs.filter(**lookup)
return qs
def liaisons(self, from_date=None, to_date=timezone.now()):
from agir.people.actions.subscription import DATE_2022_LIAISON_META_PROPERTY
liaison_form_submissions = (PersonFormSubmission.objects.filter(
person=OuterRef("pk"),
form__slug="correspondant-es-2022").order_by("created").values(
"created"))
liaisons = self.filter(
newsletters__contains=[Person.NEWSLETTER_2022_LIAISON]
).annotate(liaison_date=Coalesce(
Subquery(liaison_form_submissions[:1]),
Cast(
KeyTextTransform(DATE_2022_LIAISON_META_PROPERTY, "meta"),
DateTimeField(),
),
"created",
))
if from_date:
liaisons = liaisons.filter(liaison_date__date__gte=from_date,
liaison_date__date__lte=to_date)
return liaisons.order_by("liaison_date")
from django.db.models.fields.json import KeyTextTransform
# Imported from https://github.com/postgres/postgres/blob/REL_10_STABLE/src/bin/initdb/initdb.c#L664
TSEARCH_CONFIG_LANGUAGES = {
'da': 'danish',
'de': 'german',
'en': 'english',
'es': 'spanish',
'fi': 'finnish',
'fr': 'french',
'hu': 'hungarian',
'it': 'italian',
'nl': 'dutch',
'no': 'norwegian',
'pt': 'portuguese',
'ro': 'romanian',
'ru': 'russian',
'sv': 'swedish',
'tr': 'turkish',
}
# Imported from https://github.com/postgres/postgres/blob/REL_10_STABLE/src/bin/initdb/initdb.c#L2599
DEFAULT_TEXT_SEARCH_CONFIG = 'simple'
DOCUMENT_SEARCH_VECTOR = (
SearchVector('title', weight='A', config=F('config')) +
SearchVector(KeyTextTransform('slug', 'metadata'), weight='A', config=F('config')) +
SearchVector(KeyTextTransform('toc', 'metadata'), weight='B', config=F('config')) +
SearchVector(KeyTextTransform('body', 'metadata'), weight='C', config=F('config')) +
SearchVector(KeyTextTransform('parents', 'metadata'), weight='D', config=F('config'))
)
