def with_neighbours(self):
return self.annotate(
# previous=Subquery(
# Meteor.objects.filter(
# timestamp__lt=OuterRef('timestamp'),
# ).order_by(
# '-timestamp'
# ).values('name')[:1]
# ),
# next=Subquery(
# Meteor.objects.filter(
# timestamp__gt=OuterRef('timestamp'),
# ).order_by(
# 'timestamp'
# ).values('name')[:1]
# ),
previous=Window(
expression=Lead('name', offset=1, default=None),
order_by=F('timestamp').desc(),
),
next=Window(
expression=Lead('name', offset=1, default=None),
order_by=F('timestamp').asc(),
),
)
def test_lead_default(self):
qs = Employee.objects.annotate(lead_default=Window(
expression=Lead(expression='salary', offset=5, default=60000),
partition_by=F('department'),
order_by=F('department').asc(),
))
self.assertEqual(list(qs.values_list('lead_default', flat=True).distinct()), [60000])
def test_lead_offset(self):
"""
Determine what the person hired after someone makes. Due to
ambiguity, the name is also included in the ordering.
"""
qs = Employee.objects.annotate(lead=Window(
expression=Lead('salary', offset=2),
partition_by='department',
order_by=F('hire_date').asc(),
))
self.assertQuerysetEqual(qs, [
('Jones', 45000, 'Accounting', datetime.date(2005, 11, 1), 37000),
('Jenson', 45000, 'Accounting', datetime.date(2008, 4, 1), 50000),
('Williams', 37000, 'Accounting', datetime.date(2009, 6, 1), None),
('Adams', 50000, 'Accounting', datetime.date(2013, 7, 1), None),
('Wilkinson', 60000, 'IT', datetime.date(2011, 3, 1), None),
('Moore', 34000, 'IT', datetime.date(2013, 8, 1), None),
('Johnson', 80000, 'Management', datetime.date(2005, 7, 1), None),
('Miller', 100000, 'Management', datetime.date(2005, 6, 1), None),
('Smith', 38000, 'Marketing', datetime.date(2009, 10, 1), None),
('Johnson', 40000, 'Marketing', datetime.date(2012, 3, 1), None),
('Smith', 55000, 'Sales', datetime.date(2007, 6, 1), None),
('Brown', 53000, 'Sales', datetime.date(2009, 9, 1), None),
], transform=lambda row: (row.name, row.salary, row.department, row.hire_date, row.lead),
ordered=False
)
def test_lead(self):
"""
Determine what the next person hired in the same department makes.
Because the dataset is ambiguous, the name is also part of the
ordering clause. No default is provided, so None/NULL should be
returned.
"""
qs = Employee.objects.annotate(lead=Window(
expression=Lead(expression='salary'),
order_by=[F('hire_date').asc(), F('name').desc()],
partition_by='department',
)).order_by('department')
self.assertQuerysetEqual(qs, [
('Jones', 45000, 'Accounting', datetime.date(2005, 11, 1), 45000),
('Jenson', 45000, 'Accounting', datetime.date(2008, 4, 1), 37000),
('Williams', 37000, 'Accounting', datetime.date(2009, 6, 1), 50000),
('Adams', 50000, 'Accounting', datetime.date(2013, 7, 1), None),
('Wilkinson', 60000, 'IT', datetime.date(2011, 3, 1), 34000),
('Moore', 34000, 'IT', datetime.date(2013, 8, 1), None),
('Miller', 100000, 'Management', datetime.date(2005, 6, 1), 80000),
('Johnson', 80000, 'Management', datetime.date(2005, 7, 1), None),
('Smith', 38000, 'Marketing', datetime.date(2009, 10, 1), 40000),
('Johnson', 40000, 'Marketing', datetime.date(2012, 3, 1), None),
('Smith', 55000, 'Sales', datetime.date(2007, 6, 1), 53000),
('Brown', 53000, 'Sales', datetime.date(2009, 9, 1), None),
], transform=lambda row: (row.name, row.salary, row.department, row.hire_date, row.lead))
def test_function_list_of_values(self):
qs = Employee.objects.annotate(lead=Window(
expression=Lead(expression='salary'),
order_by=[F('hire_date').asc(),
F('name').desc()],
partition_by='department',
)).values_list('name', 'salary', 'department', 'hire_date', 'lead')
qs = fix_ordering_for_mariadb(
qs, ('department', F('hire_date').asc(), F('name').desc()))
self.assertNotIn('GROUP BY', str(qs.query))
self.assertSequenceEqual(qs, [
('Jones', 45000, 'Accounting', datetime.date(2005, 11, 1), 45000),
('Jenson', 45000, 'Accounting', datetime.date(2008, 4, 1), 37000),
('Williams', 37000, 'Accounting', datetime.date(2009, 6,
1), 50000),
('Adams', 50000, 'Accounting', datetime.date(2013, 7, 1), None),
('Wilkinson', 60000, 'IT', datetime.date(2011, 3, 1), 34000),
('Moore', 34000, 'IT', datetime.date(2013, 8, 1), None),
('Miller', 100000, 'Management', datetime.date(2005, 6, 1), 80000),
('Johnson', 80000, 'Management', datetime.date(2005, 7, 1), None),
('Smith', 38000, 'Marketing', datetime.date(2009, 10, 1), 40000),
('Johnson', 40000, 'Marketing', datetime.date(2012, 3, 1), None),
('Smith', 55000, 'Sales', datetime.date(2007, 6, 1), 53000),
('Brown', 53000, 'Sales', datetime.date(2009, 9, 1), None),
])
def _navigation_base(filter_class_function, reverse_url_function, user, obj, url_name):
context = {"current_element": obj}
search_parameters = SearchParametersCache(user, obj.__class__.__name__).cached_data
if not search_parameters:
return context
search_type = search_parameters.get("search_type")
filter_form_class = filter_class_function(search_type)
order_by = filter_form_class(data=search_parameters).qs.query.order_by
order_by_expressions = convert_order_by_strings_to_expressions(order_by) or None
qs = filter_form_class(data=search_parameters).qs.annotate(
previous_acronym=Window(
expression=Lag("acronym"),
order_by=order_by_expressions,
),
next_acronym=Window(
expression=Lead("acronym"),
order_by=order_by_expressions,
),
previous_id=Window(
expression=Lag("id"),
order_by=order_by_expressions,
),
next_id=Window(
expression=Lead("id"),
order_by=order_by_expressions,
)
).values_list(
"id",
"acronym",
"previous_acronym",
"previous_id",
"next_acronym",
"next_id",
named=True
).order_by(*order_by)
current_row = _get_current_row(qs, obj)
if current_row:
context.update({
"next_element_title": current_row.next_acronym,
"next_url": reverse_url_function(current_row.next_id, url_name) if current_row.next_id else None,
"previous_element_title": current_row.previous_acronym,
"previous_url": reverse_url_function(current_row.previous_id, url_name) if current_row.previous_id else None
})
return context
def handle(self, *args, **options):
ts_values = self.get_timeseries_values()
for td in StatewideTotalDate.objects.all().order_by('scrape_date'):
if td.scrape_date in ts_values:
td.cumulative_icu = ts_values[td.scrape_date]['total_icu_admissions']
else:
td.cumulative_icu = 0
td.save()
# Now calculate daily change
for ntd in StatewideTotalDate.objects.all().annotate(
prev_cumulative_icu=Window(
expression=Lead('cumulative_icu', offset=1, default=0),
order_by=F('scrape_date').desc()
),
difference=F('cumulative_icu')-F('prev_cumulative_icu')
).order_by('scrape_date'):
ntd.icu_total_daily_change = ntd.difference
ntd.save()
def test_null_source_lead(self):
msg = "Lead requires a non-null source expression"
with self.assertRaisesMessage(ValueError, msg):
Lead(expression=None)
def test_lead_negative_offset(self):
msg = "Lead requires a positive integer for the offset"
with self.assertRaisesMessage(ValueError, msg):
Lead(expression="salary", offset=-1)
def get_neighbour_pks(model, pk, filterset=None, ordering=None):
'''
Given a model and pk that identify an object (model instance) will, given an ordering
(defaulting to the models ordering) and optionally a filterset (from url_filter), will
return a tuple that contains two PKs that of the prior and next neighbour in the list
either of all objects by that ordering or the filtered list (if a filterset is provided)
:returns: a 4 tuple containing (prior_pk, next_pk, row_number, list_length)
:param model: The model the object is an instance of
:param pk: The primary key of the model instance being considered
:param filterset: An optional filterset (see https://github.com/miki725/django-url-filter)
:param ordering: An optional ordering (otherwise default model ordering is used). See: https://docs.djangoproject.com/en/2.0/ref/models/options/#ordering
'''
# If a filterset is provided ensure it's of the same model as specified (consistency).
if filterset and not filterset.Meta.model == model:
return (None, None)
# Get the ordering list for the model (a list of fields
# See: https://docs.djangoproject.com/en/2.0/ref/models/options/#ordering
if ordering is None:
ordering = model._meta.ordering
order_by = []
for f in ordering:
if f.startswith("-"):
order_by.append(F(f[1:]).desc())
else:
order_by.append(F(f).asc())
# A default order. We need an order or the window functions crash
if len(order_by) == 0:
order_by = ['pk']
# Define the window functions for each neighbour
window_lag = Window(expression=Lag("pk"), order_by=order_by)
window_lead = Window(expression=Lead("pk"), order_by=order_by)
window_rownnum = Window(expression=RowNumber(), order_by=order_by)
# Get a queryset annotated with neighbours. If annotated attrs clash with existing attrs an exception
# will be raised: https://code.djangoproject.com/ticket/11256
try:
# Start with all objects
qs = model.objects.all()
# Now apply a filterset if we have one
if not filterset is None:
# We respect the filterset. BUT we need to wrap it inside a sub query, so that
# we can apply a DISTNCT ON Pk to avoid duplicate tuples that the window
# functions can introduce when we are matching multiple remote objects.
# Alas that's what they do. So we have to constrain it to one tuple per
# PK.
#
# FIXME: Aaargh this won't work for injecting the current PK into the query!
# My desire is to make sure that the query results include the provided pk.
# Needs testing in both cases. I can't think of a way to do it alas. This is
# frustrating me. Problem is across related object filters, or JOINS.
# qs = filterset.filter() | (model.objects.filter(pk=pk).distinct() & filterset.filter())
qs = qs.filter(pk__in=Subquery(filterset.filter().distinct(
'pk').order_by('pk').values('pk')))
# Now order the objects properly
qs = qs.order_by(*order_by)
# Now annotate the queryset with the prior and next PKs
qs = qs.annotate(neighbour_prior=window_lag,
neighbour_next=window_lead,
row_number=window_rownnum)
except:
return None
# Finally we need some trickery alas to do a query on the queryset! We can't add this WHERE
# as a filter because the LAG and LEAD Window functions fail then, they are empty because
# there is no lagger or leader on the one line result! So we have to run that query on the
# whole table, then extract from the result the one line we want! Wish I could find a way to
# do this in the Django ORM not with a raw() call.
# First we need the SQL from the existing query. Many on-line sources seem to recommend
# str(qs.query) but this does not return reliable SQL! A bug in Django and much discussed:
# https://code.djangoproject.com/ticket/30132
# https://code.djangoproject.com/ticket/25705
# https://code.djangoproject.com/ticket/25092
# https://code.djangoproject.com/ticket/24991
# https://code.djangoproject.com/ticket/17741
#
# But this, it seems is the reliable method which involves dipping into Django's
# innards a litte (the SQL compiler)
sql, params = qs.query.get_compiler(using=qs.db).as_sql()
# Now we wrap the SQL
sql = "SELECT * FROM ({}) ao WHERE {}={}".format(sql, model._meta.pk.name,
pk)
# And create a new QuerySet
ao = model.objects.raw(sql, params)
try:
if ao:
if len(ao) == 1:
return (ao[0].neighbour_prior, ao[0].neighbour_next,
ao[0].row_number, qs.count())
else:
raise ValueError(
"Query error: object appears more than once in neighbour hunt."
)
else:
return (None, ) * 4
except:
return (None, ) * 4
