def get_queryset(self):
# custom price order filter based on regular and discount price (if exists)
qs = super().get_queryset()
qs = qs.annotate(order_price=Case(
When(discount_price__isnull=False,
then=functions.Cast("discount_price", models.FloatField())),
default=functions.Cast("price", models.FloatField()),
))
return qs
def non_rf_indicator_queryset(program_id):
"""
A QS of indicators to create the indicator plan from
"""
return models.Indicator.objects.filter(
program_id=program_id).select_related().with_logframe_sorting(
).order_by(
'old_level_pk',
'logsort_type',
functions.Cast('logsort_a', IntegerField()),
functions.Cast('logsort_b', IntegerField()),
functions.Cast('logsort_c', IntegerField()),
)
def get_queryset(self, request):
return super().get_queryset(request).annotate(_name=functions.Concat(
models.F('cellar'),
models.Value(' '),
functions.LPad(
functions.Cast(models.F('number'),
output_field=models.CharField(
max_length=2)), 2, models.Value('0')),
output_field=models.CharField(max_length=5)))
def ranking_algorithm():
# Based on Hacker News' post rank formula
time_diff = functions.Cast(functions.Now() - F('created_time'),
fields.DurationField())
hours = dtime.duration_to_hours(duration=time_diff)
rank = ExpressionWrapper(
(F('upvote') - F('devote') + 1) / ((hours + 2)**1.8),
output_field=fields.FloatField())
return hours, rank
def get_data(self):
time_query = self.time_range(self.now)
tracks = Track.objects.filter(time_query.query, sport=self.sport)
summary = tracks.values(
time_query.time_aggregate).annotate(distance=functions.Cast(
Sum(time_query.stat_query) / 1000, IntegerField()))
x, y = time_query.fill_data(summary, time_query.time_aggregate,
"distance")
return [go.Bar(x=x, y=y, name=self.name)]
def _ensure_resources(jwp_model, resource_queryset):
"""
Given a model from mediaplatform_jwp and a queryset of CachedResource object corresponding to
that model, make sure that objects of the appropriate model exist for each CachedResource
object and that their updated timestamps are correct.
Returns a list of all JWP resource keys for resources which were updated/created.
"""
jwp_queryset = jwp_model.objects.all()
# A query which returns all the cached video resources which do not correspond to an existing
# JWP video.
new_resources = (resource_queryset.exclude(
key__in=jwp_queryset.values_list('key', flat=True)))
# Start creating a list of all JWP video object which were touched in this update
updated_jwp_keys = [v.key for v in new_resources.only('key')]
# Bulk insert objects for all new resources.
jwp_queryset.bulk_create([
jwp_model(key=resource.key,
updated=resource.data.get('updated', 0),
resource=resource) for resource in new_resources
])
# A subquery which returns the corresponding CachedResource's updated timestamp for a JWP
# Video. We cannot simply use "data__updated" here because Django by design
# (https://code.djangoproject.com/ticket/14104) does not support joined fields with update()
# but the checks incorrectly interpret "data__updated" as a join and not a transform. Until
# Django is fixed, we use a horrible workaround using RawSQL. See
# https://www.postgresql.org/docs/current/static/functions-json.html for the Postgres JSON
# operators.
matching_resource_updated = models.Subquery(
resource_queryset.filter(key=models.OuterRef('key')).values_list(
functions.Cast(expressions.RawSQL("data ->> 'updated'", []),
models.BigIntegerField()))[:1])
# Add to our list of updated JWP videos
updated_jwp_keys.extend([
v.key for v in jwp_queryset.filter(
updated__lt=matching_resource_updated).only('key')
])
# For all objects whose corresponding CachedResource's updated field is later than the object's
# updated field, update the object.
(jwp_queryset.annotate(resource_updated=matching_resource_updated).filter(
updated__lt=models.F('resource_updated')).update(
updated=models.F('resource_updated')))
return updated_jwp_keys
def ordered_media_item_queryset(self):
"""
A queryset which returns the media items for the play list with the same ordering as
:py:attr:`.media_items`.
"""
all_media_items = (MediaItem.objects.filter(
id__in=self.media_items).annotate(index=expressions.Func(
models.Value(self.media_items),
functions.Cast(models.F('id'),
output_field=models.TextField()),
function='array_position')).order_by('index'))
return all_media_items
def cast(self, to_type):
"""
Coerce an expression to a new value type.
"""
return functions.Cast(self._name, to_type)
def with_end_date(self):
"""
Returns a :class:`QuerySet` where the :attr:`validity_end` date and the
:attr:`valid_between` date range have been annotated onto the query.
The resulting annotations can be queried on like fully materialised
fields. E.g, it is possible to filter on the `valid_between` field.
.. code-block:: python
Model.objects.with_end_date().filter(
valid_between__contains=date.today(),
)
"""
# Models with a single validity date always represent some feature of a
# "parent model" and are only live for as long as that model is live.
# The `over_field` is the field on this model that is a foreign key to
# the "parent model". E.g. for a description it is the described model.
over_field = self.model._meta.get_field(self.model.validity_over)
# When we are working out the validity of the next mdoel, only models
# for the same "parent model" are considered. So this partition selects
# only the models that match on the same parent fields.
partition = [
models.F(f"{over_field.name}__{field}")
for field in over_field.related_model.identifying_fields
]
# To work out the end date efficiently an SQL window expression is used.
# The rule for models with only a validity start date is that they are
# valid up until the next model takes over. So this is the same as
# ordering the models by their start dates and then takeing the start
# date of the model that appears after this one.
window = expressions.Window(
expression=aggregates.Max("validity_start"),
partition_by=partition,
order_by=models.F("validity_start").asc(),
frame=expressions.RowRange(start=0, end=1),
)
# If the value returned by the window expression is the same as the
# model's own start date, that means there was no future model with a
# later start date. Hence, this model is at the moment valid for
# unlimited time. NULLIF returns NULL if the two values match. A day has
# to be subtracted from the final result because the end date is one day
# before the next start date.
end_date_field = functions.Cast(
functions.NullIf(window, models.F("validity_start")) - timedelta(days=1),
models.DateField(),
)
# To allow the resulting field to be queried, this must be done as part
# of a Common Table Expression (CTE) because window expressions cannot
# appear in a WHERE clause.
#
# The end date and the start date are combined together into a single
# DATERANGE field to allow using __contains operators.
with_dates_added = With(
self.annotate(
validity_end=end_date_field,
valid_between=models.Func(
models.F("validity_start"),
models.F("validity_end"),
expressions.Value("[]"),
function="DATERANGE",
output_field=TaricDateRangeField(),
),
),
)
return (
with_dates_added.join(self.model, pk=with_dates_added.col.pk)
.with_cte(with_dates_added)
.annotate(
validity_end=with_dates_added.col.validity_end,
valid_between=with_dates_added.col.valid_between,
)
)
def get_span_queryset(self, _period_step, start, end):
qs = self.get_queryset().annotate(
_date_field=db_functions.Cast(self.date_field, DateField()))
kwargs = {'_date_field__gte': start, '_date_field__lt': end}
return qs.filter(**kwargs)
