Example #1
0
    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)
Example #2
0
    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')
Example #3
0
 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"),
         ),
     ))
Example #4
0
 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')
Example #5
0
    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
Example #6
0
 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')
Example #7
0
    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()))
Example #8
0
 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()
         )
     )
Example #9
0
 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(),
             ],
         ), ))
Example #10
0
 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)
Example #11
0
 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)
Example #12
0
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
Example #13
0
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')
    )
Example #14
0
 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)
Example #15
0
    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,
        )
Example #16
0
 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)
Example #17
0
 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
Example #18
0
    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
Example #19
0
    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()
Example #20
0
 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)
Example #21
0
 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()