def annotate_usage(self, *statuses): """ Returns the current queryset annotated with usage information from requirements that reference the resource. """ from .requirement import Requirement # Generate the annotations for each possible status annotations = dict( chain.from_iterable( (('{}_count'.format(status.name.lower()), models.Count('status', filter=models.Q(status=status))), ('{}_total'.format(status.name.lower()), models.Sum('amount', filter=models.Q(status=status)))) # If no statuses are given, use them all for status in (statuses or Requirement.Status))) # This subquery fetches the count and total of all requirements for the quota requirements = (Requirement.objects.filter( service__project__consortium=models.OuterRef('consortium'), resource=models.OuterRef('resource')).order_by().values( 'service__project__consortium', 'resource').annotate(**annotations)) # Apply the annotations to the current query return self.annotate( # Coalesce the corresponding annotation from the subquery **{ annotation: functions.Coalesce( models.Subquery(requirements.values(annotation)), models.Value(0)) for annotation in annotations })
def index(request): # Ordering valid_ordering = ["query_string", "-query_string", "views", "-views"] ordering = valid_ordering[0] if "ordering" in request.GET and request.GET["ordering"] in valid_ordering: ordering = request.GET["ordering"] # Query queries = Query.objects.filter(editors_picks__isnull=False).distinct() if "views" in ordering: queries = queries.annotate( views=functions.Coalesce(Sum("daily_hits__hits"), 0)) queries = queries.order_by(ordering) # Search is_searching = False query_string = request.GET.get("q", "") if query_string: queries = queries.filter(query_string__icontains=query_string) is_searching = True # Paginate paginator = Paginator(queries, per_page=20) queries = paginator.get_page(request.GET.get("p")) if request.headers.get("x-requested-with") == "XMLHttpRequest": return TemplateResponse( request, "wagtailsearchpromotions/results.html", { "is_searching": is_searching, "ordering": ordering, "queries": queries, "query_string": query_string, }, ) else: return TemplateResponse( request, "wagtailsearchpromotions/index.html", { "is_searching": is_searching, "ordering": ordering, "queries": queries, "query_string": query_string, "search_form": SearchForm( data={"q": query_string} if query_string else None, placeholder=_("Search promoted results"), ), }, )
def get_context_data(self, **kwargs): context = super(IndexTemplateView, self).get_context_data(**kwargs) context['total_project'] = Project.objects.all().count() context['success_project'] = Project.objects.filter(status='C').count() context['progress_project'] = Project.objects.filter( status='A').count() context['late_project'] = Project.objects.filter( status='A', end_date__lt=date.today()).count() context['plan_project'] = Project.objects.filter(status='P').count() context['total_budget'] = Project.objects.all().aggregate( total_expected_budget=Sum(functions.Coalesce('expected_budget', 0), output_field=FloatField()), total_executed_budget=Sum(functions.Coalesce('executed_budget', 0), output_field=FloatField())) context['project_without_budget'] = Project.objects.filter( expected_budget__isnull=True).count() context['project_burst_budget'] = Project.objects.filter( expected_budget__lt=F('executed_budget')).count() cat = Category.objects.annotate( total_project=Count('project__id') ).extra( select={ 'progress': "CAST((SELECT COUNT(*) FROM core_project WHERE category_id = core_category.id and core_project.status = 'C') AS float)/CAST((SELECT COUNT(*) FROM core_project WHERE status = 'C') AS FLOAT)*100" }).order_by('-progress') tipos = Type.objects.values('name').annotate( total_project=Count('project__id')).order_by('-total_project') total_restante = 0 for tipo in tipos[5:]: total_restante += tipo['total_project'] context['categories'] = cat[:5] context['tipos'] = tipos[:5] context['total_restante'] = total_restante # Locais context['institutions'] = Institution.objects.extra(select={ 'projects': "SELECT COUNT(*) from core_project WHERE core_project.institution_id = core_institution.id" }, order_by=[ '-projects' ])[:5] return context
def coalesce(*args): """ Chooses the first non-null value from left to right. Usage: coalesce(F.savings_account, F.check_account, 0) """ return functions.Coalesce(*args)
def _filter_and_order_markets(self, order_by, orig_filter): if order_by == 'users': return self.markets.filter(**orig_filter).order_by('-number_of_registered_users') elif order_by == 'commission': markets = self.markets.filter(**orig_filter) annotated = markets.annotate(commision_null=functions.Coalesce('commission_lower', -1)) return annotated.order_by('commision_null', 'commission_lower') else: return self._order_markets_by_relevance(orig_filter)
def save(self, *args, **kwargs): if not self.pk: # попытка решить проблему того, что при создании коллекции, # элементы отсортированы в порядке загрузки, а не в порядке # добавления. Код ниже не решает проблему, но уменьшает её влияние. if self.collection.items.filter(order=self.order).exists(): max_order = self.collection.items.aggregate( order=functions.Coalesce(models.Max('order'), 0))['order'] self.order = max_order + 1 super().save(*args, **kwargs)
def annotate_available(self): """ Returns the current queryset annotated with information about the total amount available from all the chunks for that resource. """ # This subquery returns the total of all the chunks for the outer resource from .resource_chunk import ResourceChunk chunks = (ResourceChunk.objects.filter(resource=models.OuterRef( 'pk')).order_by().values('resource').annotate( total=models.Sum('amount'))) return self.annotate(total_available=functions.Coalesce( models.Subquery(chunks.values('total')), models.Value(0)))
def index(request): # Ordering valid_ordering = ['query_string', '-query_string', 'views', '-views'] ordering = valid_ordering[0] if 'ordering' in request.GET and request.GET['ordering'] in valid_ordering: ordering = request.GET['ordering'] # Query queries = Query.objects.filter(editors_picks__isnull=False).distinct() if 'views' in ordering: queries = queries.annotate( views=functions.Coalesce(Sum('daily_hits__hits'), 0)) queries = queries.order_by(ordering) # Search is_searching = False query_string = request.GET.get('q', '') if query_string: queries = queries.filter(query_string__icontains=query_string) is_searching = True # Paginate paginator = Paginator(queries, per_page=20) queries = paginator.get_page(request.GET.get('p')) if request.headers.get('x-requested-with') == 'XMLHttpRequest': return TemplateResponse( request, "wagtailsearchpromotions/results.html", { 'is_searching': is_searching, 'ordering': ordering, 'queries': queries, 'query_string': query_string, }) else: return TemplateResponse( request, 'wagtailsearchpromotions/index.html', { 'is_searching': is_searching, 'ordering': ordering, 'queries': queries, 'query_string': query_string, 'search_form': SearchForm(data=dict(q=query_string) if query_string else None, placeholder=_("Search promoted results")), })
def for_user(self, user, start, end): """Get employments in given time frame for current user. This includes overlapping employments. :param User user: The user of the searched employments :param datetime.date start: start of time frame :param datetime.date end: end of time frame :returns: queryset of employments """ # end date NULL on database is like employment is ending today queryset = self.annotate( end=functions.Coalesce("end_date", models.Value(date.today()))) return queryset.filter(user=user).exclude( models.Q(end__lt=start) | models.Q(start_date__gt=end))
def annotate_usage(self): """ Returns the current queryset annotated with usage information from quotas and requirements that reference the resource. """ # This subquery returns the count and total of all quotas for the outer resource from .quota import Quota quotas = (Quota.objects.filter(resource=models.OuterRef( 'pk')).order_by().values('resource').annotate( count=models.Count('*'), total=models.Sum('amount'))) # This subquery fetches the count and total of all requirements for the current resource for each status from .requirement import Requirement # Generate the annotations for each possible status subquery_annotations = dict( chain.from_iterable( (('{}_count'.format(status.name.lower()), models.Count('status', filter=models.Q(status=status))), ('{}_total'.format(status.name.lower()), models.Sum('amount', filter=models.Q(status=status)))) for status in Requirement.Status)) requirements = (Requirement.objects.filter(resource=models.OuterRef( 'pk')).order_by().values('resource').annotate( **subquery_annotations)) # Apply the annotations to the current query return self.annotate( quota_count=functions.Coalesce( models.Subquery(quotas.values('count')), models.Value(0)), quota_total=functions.Coalesce( models.Subquery(quotas.values('total')), models.Value(0)), # Coalesce the corresponding annotation from the subquery **{ annotation: functions.Coalesce( models.Subquery(requirements.values(annotation)), models.Value(0)) for annotation in subquery_annotations })
def annotate_value_or_default(self, option_name): """ then annotate_option_values will annotate the queryset with - The default value of the option as '<option_name>_default' - COALSCE of the field value and the default value annotated as '<option_name>'' - raw value of the field available as '_' + 'option_name' """ field_name = '_' + option_name default_annotation_name = option_name + '_default' return (self.annotate_defaults(option_name).annotate( **{ option_name: functions.Coalesce(field_name, default_annotation_name) }))