def get(self, request):
start = str(request.data.get('start'))
end = str(request.data.get('end'))
if not start or not end:
return Response({'msg': 'Missing params'},
status=HTTP_400_BAD_REQUEST)
try:
start = datetime.datetime.strptime(start, '%d-%m-%Y')
end = datetime.datetime.strptime(end, '%d-%m-%Y')
except:
return Response(
{'msg': 'Invalid date format, expected DD-MM-YYYY'},
status=HTTP_400_BAD_REQUEST)
movies_query = Movie.objects.annotate(
comments=Coalesce(
Sum(
Case(When(comment__timestamp__range=[start, end], then=1),
output_field=IntegerField())), 0),
rank=Window(expression=DenseRank(),
order_by=F('comments').desc())).order_by(
'-comments', 'id')
movies = [{
'id': movie.id,
'comments': movie.comments,
'rank': movie.rank
} for movie in movies_query]
return Response(movies)
def get_queryset(self):
date_from = self.request.query_params.get('date_from', None)
date_to = self.request.query_params.get('date_to', None)
try:
date_from = datetime.datetime.strptime(date_from,
"%d-%m-%Y").date()
date_to = datetime.datetime.strptime(date_to, "%d-%m-%Y").date()
if settings.USING_SQLLITE:
top_movies = Movie.objects.filter(
comment__created__range=[date_from, date_to]).annotate(
total_comments=Count('comment')).order_by(
'-total_comments')
return top_movies
else:
dense_rank_by_total_comments = Window(
expression=DenseRank(),
order_by=F("total_comments").desc())
top_movies = Movie.objects.filter(
comment__created__range=[date_from, date_to]).annotate(
total_comments=Count('comment')).annotate(
rank=dense_rank_by_total_comments).order_by(
'-total_comments')
return top_movies
except ValueError:
LOGGER.exception('Date parsing error')
def qs_with_collection(queryset: QuerySet, **_kwargs) -> QuerySet:
return queryset.annotate(sort_order=Window(
expression=DenseRank(),
order_by=(
F("collectionproduct__sort_order").asc(nulls_last=True),
F("collectionproduct__id"),
),
))
def top(self):
return super().get_queryset().values('movie', ).annotate(
total_comments=Count('id'),
rank=Window(
expression=DenseRank(),
order_by=F('total_comments').desc(),
),
).order_by('-total_comments')
def get_queryset(self):
queryset = Player.objects.annotate(rank=Window(
expression=DenseRank(),
order_by=F('points').desc(),
))
country_iso_code = self.kwargs['country_iso_code']
if country_iso_code:
queryset.filter(country_iso_code=country_iso_code.upper())
return queryset
def get_queryset(self, date_from: datetime.date, date_to: datetime.date):
return Movie.objects.annotate(
total_comments=Count(
'comments',
filter=Q(comments__created_at__range=[date_from, date_to], ),
),
rank=Window(
expression=DenseRank(),
order_by=F('total_comments').desc(),
),
).order_by('rank')
def filter_comments_after(self, query, field_name, value):
before_str = self.request.query_params.get('comments_before', None)
before = self.declared_filters['comments_before'].field.to_python(
before_str)
return query.annotate(total_comments=Count(
expression='comments',
filter=Q(comments__created_at__gt=value,
comments__created_at__lt=before)),
rank=Window(expression=DenseRank(),
order_by=F('total_comments').desc()))
def comments_ranking(self, from_date, to_date):
return self.annotate(
total_comments=models.Count(
models.Case(
models.When(comments__created__range=(from_date, to_date), then=1),
output_field=models.IntegerField(),
)
)
).annotate(
rank=models.Window(
expression=DenseRank(), order_by=models.F('total_comments').desc()
)
)
def get_queryset(self):
competition_slug = self.kwargs["competition_slug"]
try:
competition = Competition.objects.get(slug=competition_slug)
except Competition.DoesNotExist:
raise CompetitionDoesNotExist
return (competition.get_results().select_related("team").order_by(
"-result").annotate(place=Window(
expression=DenseRank(),
order_by=[
F("result").desc(),
],
), ))
def test_invalid_filter(self):
msg = 'Window is disallowed in the filter clause'
qs = Employee.objects.annotate(dense_rank=Window(expression=DenseRank()))
with self.assertRaisesMessage(NotSupportedError, msg):
qs.filter(dense_rank__gte=1)
with self.assertRaisesMessage(NotSupportedError, msg):
qs.annotate(inc_rank=F('dense_rank') + Value(1)).filter(inc_rank__gte=1)
with self.assertRaisesMessage(NotSupportedError, msg):
qs.filter(id=F('dense_rank'))
with self.assertRaisesMessage(NotSupportedError, msg):
qs.filter(id=Func('dense_rank', 2, function='div'))
with self.assertRaisesMessage(NotSupportedError, msg):
qs.annotate(total=Sum('dense_rank', filter=Q(name='Jones'))).filter(total=1)
def test_conditional_annotation(self):
qs = Employee.objects.annotate(
dense_rank=Window(expression=DenseRank()),
).annotate(
equal=Case(
When(id=F('dense_rank'), then=Value(True)),
default=Value(False),
output_field=BooleanField(),
),
)
# The SQL standard disallows referencing window functions in the WHERE
# clause.
msg = 'Window is disallowed in the filter clause'
with self.assertRaisesMessage(NotSupportedError, msg):
qs.filter(equal=True)
def filter_top_movies(date_from: datetime.date, date_to: datetime.date):
"""Return all movies with total_comments and rank fields.
total_comments is a total number of comments that were added between specified date range
"""
comments_within_date_range = Q(
comments__created_at__date__gte=date_from) & Q(
comments__created_at__date__lte=date_to)
qs = Movie.objects.annotate(total_comments=Count(
"comments", filter=comments_within_date_range),
rank=Window(
expression=DenseRank(),
order_by=F("total_comments").desc(),
))
return qs
def get_ranked_movies(start_date, end_date):
return (
Movie.objects.all()
# Renaming id to movie_id
.annotate(movie_id=F('id'),
# Only count comments in the range
total_comments=Count('comment', filter=Q(comment__date__range=(start_date, end_date))))
# Rank is higher for a movie with lower number of comments
# Equal for same number
# Is dense (no gaps)
# Equals 1 for the most commented movie
.annotate(rank=Window(
expression=DenseRank(),
order_by=F('total_comments').desc(),
))
.order_by('rank')
.values('movie_id', 'total_comments', 'rank')
)
def test_dense_rank(self):
qs = Employee.objects.annotate(rank=Window(
expression=DenseRank(),
order_by=ExtractYear(F('hire_date')).asc(),
))
self.assertQuerysetEqual(qs, [
('Jones', 45000, 'Accounting', datetime.date(2005, 11, 1), 1),
('Miller', 100000, 'Management', datetime.date(2005, 6, 1), 1),
('Johnson', 80000, 'Management', datetime.date(2005, 7, 1), 1),
('Smith', 55000, 'Sales', datetime.date(2007, 6, 1), 2),
('Jenson', 45000, 'Accounting', datetime.date(2008, 4, 1), 3),
('Smith', 38000, 'Marketing', datetime.date(2009, 10, 1), 4),
('Brown', 53000, 'Sales', datetime.date(2009, 9, 1), 4),
('Williams', 37000, 'Accounting', datetime.date(2009, 6, 1), 4),
('Wilkinson', 60000, 'IT', datetime.date(2011, 3, 1), 5),
('Johnson', 40000, 'Marketing', datetime.date(2012, 3, 1), 6),
('Moore', 34000, 'IT', datetime.date(2013, 8, 1), 7),
('Adams', 50000, 'Accounting', datetime.date(2013, 7, 1), 7),
], lambda entry: (entry.name, entry.salary, entry.department, entry.hire_date, entry.rank), ordered=False)
def get(self, request, *args, **kwargs):
qs = Drug.objects.prefetch_related('interactions').annotate(
interactions_count=Count('interactions'),
rank=Window(
expression=DenseRank(),
order_by=F('interactions_count').desc(),
),
).order_by('rank')[:10]
return Response(
[
{
'name': drug.name,
'rxcui': drug.rxcui,
'interactions_count': drug.interactions_count,
'rank': drug.rank,
} for drug in qs
],
status=status.HTTP_200_OK,
)
def get(self, request):
"""Extract dates range from query_params and return top movies."""
start, end = self.get_start_end_date_from_request(request)
# pull ranked movies sorted by number of comments in given date range
movies_query = Movie.objects.annotate(
total_comments=Coalesce(
Sum(
Case(When(comment__created__range=[start, end], then=1),
output_field=IntegerField())), 0),
rank=Window(
expression=DenseRank(),
order_by=F('total_comments').desc(),
)).order_by('-total_comments', 'id')
# extract needed fields to response
# it's too trivial to use Serializer here
movies = [{
'movie_id': movie.id,
'total_comments': movie.total_comments,
'rank': movie.rank
} for movie in movies_query]
return Response(movies)
def get_queryset(self):
"""
Window function 'DenseRank' used to provide
rank values with desired behaviour.
Raising custom exceptions enables 'get()' function
to return proper error messages.
"""
date_from = self.request.GET.get('from')
date_to = self.request.GET.get('to')
if not date_from or not date_to:
raise NoDateRangeException()
window = Window(expression=DenseRank(), order_by=F('comment_count').desc())
try:
date_from = datetime.strptime(date_from, '%Y-%m-%d').date()
date_to = datetime.strptime(date_to, '%Y-%m-%d').date()
except ValueError:
raise BadDateFormatException()
queryset = Movie.objects.annotate(
comment_count=Count('comments',
filter=Q(comments__created__date__range=(date_from, date_to))),
rank=window)
return queryset
def get_context_data(self, **kwargs):
context = super().get_context_data(**kwargs)
# number of single words in album
context['single_word'] = (Word.objects.filter(
artist_id=self.object.pk).values('album__name').annotate(
total=Count('id')).order_by('-total')[0:10])
# words that appear in most songs
# we want the word and in how many song it appears
"""WITH distinct_song AS (SELECT DISTINCT value, song_id FROM kyo_word WHERE artist_id=6)
SELECT value, COUNT(*) FROM distinct_song GROUP BY value ORDER BY count(*) DESC LIMIT 20; """
# Rank albums by popularity
dense_rank_by_album = Window(expression=DenseRank(),
order_by=F("popularity").desc())
context['album_by_popularity'] = (Album.objects.filter(
artist=self.object).annotate(
ranking=dense_rank_by_album).order_by('ranking'))
# Words with rank on their frequency
dense_rank_by_album = Window(expression=DenseRank(),
partition_by=F("album_id"),
order_by=F("frequency").desc())
context['words'] = (Word.objects.filter(artist_id=self.object).values(
'value', 'album_id').annotate(
frequency=Count('value'),
ranking=dense_rank_by_album).order_by('ranking'))
# top 10 words per album
# Adding a where on ranking won't work, would need to put it in a subquery
# So let's do it raw SQL
query = """
SELECT value, a.name as album_name, frequency, ranking FROM (
SELECT value,
album.name,
count(*) as frequency,
dense_rank() OVER (PARTITION BY album.id
ORDER BY COUNT(*)
DESC
) ranking
FROM kyo_word INNER JOIN kyo_album album ON album.id = kyo_word.album_id WHERE kyo_word.artist_id = %s AND value <> 'refrain' GROUP BY value, album.id ORDER BY album.id) a
WHERE a.ranking < 9 AND a.frequency > 5;"""
with connection.cursor() as cursor:
cursor.execute(query, [self.object.pk])
context['top_10_words'] = dictfetchall(cursor)
# album with the next
query = """
SELECT album.id, album.year, album.popularity,
next_album.id as next_album_pk,
next_album.name as next_album_name,
next_album.year as next_album_year
FROM kyo_album album
LEFT OUTER JOIN LATERAL (
SELECT * FROM kyo_album next_album
WHERE next_album.artist_id=album.artist_id
AND next_album.year > album.year
ORDER BY year ASC LIMIT 1) next_album on true
WHERE album.artist_id=%s
ORDER BY album.year;"""
context['albums'] = Album.objects.raw(query, [self.object.pk])
# top words total
context['top_words'] = Word.objects.filter(
artist_id=self.object.pk).values('value').annotate(
total=Count('id')).order_by('-total')[:30]
return context
def execute_query(self): # noqa: C901
"""Execute query and return provided data.
Returns:
(Dict): Dictionary response of query params, data, and total
"""
query_sum = {'value': 0}
data = []
with tenant_context(self.tenant):
query_filter = self._get_filter()
query_group_by = self._get_group_by()
query_annotations = self._get_annotations()
query_order_by = ('-date', )
if self.is_sum:
query_filter = self._strip_table_references(query_filter)
query = AWSCostEntryLineItemDailySummary.objects.filter(
**query_filter)
else:
query = AWSCostEntryLineItem.objects.filter(**query_filter)
query_data = query.annotate(**query_annotations)
query_group_by = ['date'] + query_group_by
query_group_by_with_units = query_group_by + ['units']
if self.is_sum:
query_order_by += (self.order, )
query_data = query_data.values(*query_group_by_with_units)\
.annotate(total=Sum(self.aggregate_key))
if self.count and self.is_sum:
# This is a sum because the summary table already
# has already performed counts
query_data = query_data.annotate(count=Sum(self.count))
if self._limit and self.is_sum:
rank_order = getattr(F(self.order_field),
self.order_direction)()
dense_rank_by_total = Window(expression=DenseRank(),
partition_by=F('date'),
order_by=rank_order)
query_data = query_data.annotate(rank=dense_rank_by_total)
query_order_by = query_order_by + ('rank', )
query_data = query_data.order_by(*query_order_by)
is_csv_output = self._accept_type and 'text/csv' in self._accept_type
if self.is_sum and not is_csv_output:
data = self._apply_group_by(list(query_data))
data = self._transform_data(query_group_by, 0, data)
elif is_csv_output and self.is_sum:
values_out = query_group_by_with_units + ['total']
if self._limit:
data = self._ranked_list(list(query_data))
else:
data = list(query_data)
else:
values_out = query_group_by_with_units + EXPORT_COLUMNS
data = list(query_data.values(*values_out))
if query.exists():
units_value = query.values(self.units_key).first().get(
self.units_key)
query_sum = self.calculate_total(units_value)
if self._delta:
self.query_delta = self.calculate_delta(
self._delta, query, query_sum, **query_filter)
self.query_sum = query_sum
self.query_data = data
return self._format_query_response()
def test_invalid_filter(self):
msg = 'Window is disallowed in the filter clause'
with self.assertRaisesMessage(NotSupportedError, msg):
Employee.objects.annotate(dense_rank=Window(expression=DenseRank())).filter(dense_rank__gte=1)
def test_unsupported_backend(self):
msg = 'This backend does not support window expressions.'
with mock.patch.object(connection.features, 'supports_over_clause', False):
with self.assertRaisesMessage(NotSupportedError, msg):
Employee.objects.annotate(dense_rank=Window(expression=DenseRank())).get()