def get_queryset(self) -> models.QuerySet:
qs = self.queryset.annotate(has_user=expressions.Case(
expressions.When(
user__isnull=False,
then=expressions.Value(True),
),
default=expressions.Value(False),
#
# Tell Django the expected type of the field, see `output_field` in
# https://docs.djangoproject.com/en/2.1/ref/models/expressions/
#
output_field=models.BooleanField()))
logger.info(qs.query)
return qs
def IsNotNone(*fields, default=None):
"""Selects whichever field is not None, in the specified order.
Arguments:
fields:
The fields to attempt to get a value from,
in order.
default:
The value to return in case all values are None.
Returns:
A Case-When expression that tries each field and
returns the specified default value when all of
them are None.
"""
when_clauses = [
expressions.When(~expressions.Q(**{field: None}),
then=expressions.F(field))
for field in reversed(fields)
]
return expressions.Case(
*when_clauses,
default=expressions.Value(default),
output_field=CharField(),
)
def _search_by_keywords_no_sort(self, keywords):
now = datetime.now().timestamp()
# Use expressions.Value to represent search_vector column.
# This can avoid create the search vector for each request.
# The search_vector has already added as a SearchVectorField and updated by the trigger, we can use it directly.
products = self.annotate(rank=SearchRank(expressions.Value('search_vector'), keywords)) \
.filter(active=True,
promotion_start_date__lte=now,
promotion_end_date__gt=now,
search_vector=keywords)
return products
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,
)
)