def place_list(self, limit, offset): hardcoded = [x.place for x in NeighborhoodEntry.objects.filter(neighborhood=self).order_by('rank')] if offset == 0: to_fetch = (limit - len(hardcoded)) + 1 else: to_fetch = limit + 1 if self.bounds: close_by = Place.objects.filter( Q(geom__within=self.bounds) ).annotate( has_card=models.Count('gift_card_url') ).exclude( place_id__in=[x.place_id for x in hardcoded] ).order_by('-has_card', '-num_ratings')[offset:offset + to_fetch] else: close_by = Place.objects.filter( Q(geom__distance_lt=(self.geom, D(m=2500))) ).exclude( place_id__in=[x.place_id for x in hardcoded] ).annotate( has_card=models.Count('gift_card_url') ).annotate( distance=Distance('geom', self.geom) ).order_by('-has_card', 'distance')[offset:offset + to_fetch] more_available = len(close_by) == to_fetch if offset == 0: joined = (hardcoded + list(close_by)) else: joined = list(close_by) end_list = -1 if more_available else len(joined) return joined[0:end_list], more_available
def complainant_race_aggregation(self): query_set = Complainant.objects.filter( allegation__officerallegation__officer__officerhistory__unit=self ).distinct().annotate(name=models.Case( models.When(race__isnull=True, then=models.Value('Unknown')), models.When(race__in=['n/a', 'n/a ', 'nan', ''], then=models.Value('Unknown')), default='race', output_field=models.CharField())).values('name').annotate( count=models.Count('id', distinct=True)) sustained_count_query_set = Complainant.objects.filter( allegation__officerallegation__officer__officerhistory__unit=self, allegation__officerallegation__final_finding='SU').distinct( ).annotate(name=models.Case( models.When(race__isnull=True, then=models.Value('Unknown')), models.When(race__in=['n/a', 'n/a ', 'nan', ''], then=models.Value('Unknown')), default='race', output_field=models.CharField())).values('name').annotate( sustained_count=models.Count('id', distinct=True)) sustained_count_results = { obj['name']: obj['sustained_count'] for obj in sustained_count_query_set } return [{ 'name': obj['name'], 'count': obj['count'], 'sustained_count': sustained_count_results.get(obj['name'], 0) } for obj in query_set if obj['count'] > 0]
def compute_total(self): aggs = self.pharmapayment_set.all().aggregate(models.Sum('amount'), models.Count('pharma_company', distinct=True)) self.total = aggs['amount__sum'] or decimal.Decimal(0) self.total_currency = PharmaPayment.ORIGIN_CURRENCY[self.origin] self.company_count = aggs['pharma_company__count'] year_aggs = self.pharmapayment_set.all().values('date').annotate( amount=models.Sum('amount'), company_count=models.Count('pharma_company', distrinct=True) ) total_euro = decimal.Decimal(0.0) for year_agg in year_aggs: year = year_agg['date'].year self.aggs['total_%s' % year] = float(year_agg['amount']) year_euro = convert_currency_to_euro(year_agg['amount'], self.total_currency, year) total_euro += year_euro self.aggs['total_euro_%s' % year] = float(year_euro) self.aggs['company_count_%s' % year] = year_agg['company_count'] if self.is_zerodoc: efpia_years = self.zerodoctor.get_confirmed_efpia_years() for year in efpia_years: year_key = 'total_%s' % year if self.aggs.get(year_key): raise ValueError('Conflict detected at %s' % self.pk) self.aggs[year_key] = 0 self.aggs['total_euro_%s' % year] = 0 self.total_euro = total_euro
def complainant_gender_aggregation(self): query_set = Complainant.objects.filter( allegation__officerallegation__officer__officerhistory__unit=self ).distinct().annotate(complainant_gender=models.Case( models.When(gender='', then=models.Value('Unknown')), default='gender', output_field=models.CharField())).values( 'complainant_gender').annotate( count=models.Count('id', distinct=True)) sustained_count_query_set = Complainant.objects.filter( allegation__officerallegation__officer__officerhistory__unit=self, allegation__officerallegation__final_finding='SU').distinct( ).annotate(complainant_gender=models.Case( models.When(gender='', then=models.Value('Unknown')), default='gender', output_field=models.CharField())).values( 'complainant_gender').annotate( sustained_count=models.Count('id', distinct=True)) sustained_count_results = { obj['complainant_gender']: obj['sustained_count'] for obj in sustained_count_query_set } return [{ 'name': GENDER_DICT.get(obj['complainant_gender'], 'Unknown'), 'count': obj['count'], 'sustained_count': sustained_count_results.get(obj['complainant_gender'], 0) } for obj in query_set if obj['count'] > 0]
def complainant_gender_aggregation(self): query = self.officerallegation_set.all() query = query.values('allegation__complainant__gender').annotate( complainant_gender=models.Case( models.When(allegation__complainant__gender='', then=models.Value('Unknown')), models.When(allegation__complainant__isnull=True, then=models.Value('Unknown')), default='allegation__complainant__gender', output_field=models.CharField()), year=ExtractYear('start_date')) query = query.values('complainant_gender', 'year').order_by( 'complainant_gender', 'year').annotate( count=models.Count('complainant_gender'), sustained_count=models.Sum( models.Case(models.When(final_finding='SU', then=1), default=models.Value(0), output_field=models.IntegerField()))) data = [{ 'name': GENDER_DICT.get(obj['complainant_gender'], 'Unknown'), 'sustained_count': obj['sustained_count'], 'count': obj['count'], 'year': obj['year'] } for obj in query if obj['count'] > 0] return Officer._group_and_sort_aggregations(data)
def statistics(self): queryset = self.viewpoints.annotate( missing=models.Count('pk', filter=models.Q( pictures__isnull=True )), pending=models.Count('pictures', filter=models.Q( pictures__state=STATES.DRAFT )), refused=models.Count('pictures', filter=models.Q( pictures__state=STATES.REFUSED )), ).values('missing', 'pending', 'refused') try: return queryset[0] except IndexError: return {'missing': 0, 'pending': 0, 'refused': 0}
def with_stats(self): return self.annotate( viewpoints_total=models.Count("viewpoints", distinct=True), pictures_submited=models.Count( "pictures__pk", filter=models.Q(pictures__state=Picture.SUBMITTED), distinct=True, ), pictures_accepted=models.Count( "pictures__pk", filter=models.Q(pictures__state=Picture.ACCEPTED), distinct=True, ), pictures_missing=models.F("viewpoints_total") - models.F("pictures_submited") - models.F("pictures_accepted"), )
def _honorable_mention_count_query(_, max_datetime): return models.Count(models.Case( models.When(award__start_date__lte=max_datetime.date(), award__award_type='Honorable Mention', then='award'), output_field=models.CharField(), ), distinct=True)
def _allegation_internal_count_query(min_datetime, max_datetime): return models.Count(models.Case( models.When(Q( officerallegation__allegation__is_officer_complaint=True, officerallegation__allegation__incident_date__gte=min_datetime, officerallegation__allegation__incident_date__lte=max_datetime), then='officerallegation')), distinct=True)
def _trr_count_query(min_datetime, max_datetime): return models.Count(models.Case( models.When(Q(trr__trr_datetime__date__gte=min_datetime, trr__trr_datetime__date__lte=max_datetime), then='trr'), output_field=models.CharField(), ), distinct=True)
def _allegation_count_query(min_datetime, max_datetime): return models.Count(models.Case( models.When(Q( officerallegation__allegation__incident_date__gte=min_datetime, officerallegation__allegation__incident_date__lte=max_datetime), then='officerallegation'), output_field=models.CharField(), ), distinct=True)
def member_age_aggregation(self): query_set = Officer.objects.filter(officerhistory__unit=self).distinct( ).annotate(member_age=models.Case( models.When(birth_year__isnull=True, then=None), default=now().year - F('birth_year'), output_field=models.IntegerField())).annotate( name=get_num_range_case('member_age', [0, 20, 30, 40, 50]) ).values('name').annotate(count=models.Count('id', distinct=True)) return list(query_set)
def with_rank(self): ranked = UrbanInstituteRentalCrisisData.objects.annotate( rank=models.Window(expression=Rank(), partition_by=[models.F('year')], order_by=(models.F('aaa_units') / models.F('eli_renters')).desc()), total=models.Window(expression=models.Count(['year']), partition_by=[models.F('year')])) return ranked
def member_race_aggregation(self): query_set = Officer.objects.filter( officerhistory__unit=self).distinct().annotate(name=models.Case( models.When(race__isnull=True, then=models.Value('Unknown')), models.When(race__in=['n/a', 'n/a ', 'nan', ''], then=models.Value('Unknown')), default='race', output_field=models.CharField())).values('name').annotate( count=models.Count('id', distinct=True)) return list(query_set)
def date_counts(self): """ Returns a dictionary mapping {item_date: count}. """ # TODO: values + annotate doesn't seem to play nice with GeoQuerySet # at the moment. This is the changeset where it broke: # http://code.djangoproject.com/changeset/10326 from django.db.models.query import QuerySet qs = QuerySet.values(self, 'item_date').annotate(count=models.Count('id')) return dict([(v['item_date'], v['count']) for v in qs])
def complainant_age_aggregation(self): query = self.officerallegation_set.all() query = query.annotate(name=get_num_range_case( 'allegation__complainant__age', [0, 20, 30, 40, 50]), year=ExtractYear('start_date')) query = query.values('name', 'year').order_by('name', 'year').annotate( count=models.Count('name'), sustained_count=models.Sum( models.Case(models.When(final_finding='SU', then=1), default=models.Value(0), output_field=models.IntegerField()))) return Officer._group_and_sort_aggregations(list(query))
def member_gender_aggregation(self): query_set = Officer.objects.filter(officerhistory__unit=self).distinct( ).annotate(member_gender=models.Case( models.When(gender='', then=models.Value('Unknown')), models.When(gender__isnull=True, then=models.Value('Unknown')), default='gender', output_field=models.CharField())).values('member_gender').annotate( count=models.Count('id', distinct=True)) return [{ 'name': GENDER_DICT.get(obj['member_gender'], 'Unknown'), 'count': obj['count'] } for obj in query_set if obj['count'] > 0]
def t_shirt_size_counts(self): if not self.pk: package_transactions = self.campaign.user_attendances_for_delivery( ) t_shirts = TShirtSize.objects.filter( userattendance__in=package_transactions) t_shirts = t_shirts.annotate( size_count=models.Count('userattendance')) else: package_transactions = PackageTransaction.objects.filter( team_package__box__delivery_batch=self) t_shirts = TShirtSize.objects.filter( packagetransaction__in=package_transactions) t_shirts = t_shirts.annotate( size_count=models.Count('packagetransaction')) t_shirt_counts = t_shirts.values_list('name', 'size_count') ordered_t_shirt_counts = collections.OrderedDict([ (size.name, 0) for size in TShirtSize.objects.filter( campaign=self.campaign, ).order_by('name') ]) for count in t_shirt_counts: ordered_t_shirt_counts[count[0]] = count[1] return ordered_t_shirt_counts.items()
def complainant_age_aggregation(self): query_set = Complainant.objects.filter( allegation__officerallegation__officer__officerhistory__unit=self ).distinct().annotate(name=get_num_range_case( 'age', [0, 20, 30, 40, 50])).values('name').annotate( count=models.Count('id', distinct=True)).order_by('name') sustained_count_query_set = Complainant.objects.filter( allegation__officerallegation__officer__officerhistory__unit=self, allegation__officerallegation__final_finding='SU').distinct( ).annotate(name=get_num_range_case( 'age', [0, 20, 30, 40, 50])).values('name').annotate( sustained_count=models.Count('id', distinct=True)) sustained_count_results = { obj['name']: obj['sustained_count'] for obj in sustained_count_query_set } return [{ 'name': obj['name'], 'count': obj['count'], 'sustained_count': sustained_count_results.get(obj['name'], 0) } for obj in query_set if obj['count'] > 0]
def complaint_category_aggregation(self): query_set = OfficerAllegation.objects.filter( officer__officerhistory__unit=self).distinct().annotate( name=models.Case( models.When(allegation_category__category__isnull=True, then=models.Value('Unknown')), default='allegation_category__category', output_field=models.CharField())).values('name').annotate( count=models.Count('allegation__crid', distinct=True), sustained_count=models.Sum( models.Case(models.When(final_finding='SU', then=1), default=0, output_field=models.IntegerField()))) return list(query_set)
def complaint_category_aggregation(self): query = self.officerallegation_set.all() query = query.annotate(name=models.Case( models.When(allegation_category__category__isnull=True, then=models.Value('Unknown')), default='allegation_category__category', output_field=models.CharField()), year=ExtractYear('start_date')) query = query.values('name', 'year').order_by('name', 'year').annotate( count=models.Count('name'), sustained_count=models.Sum( models.Case(models.When(final_finding='SU', then=1), default=models.Value(0), output_field=models.IntegerField()))) return Officer._group_and_sort_aggregations(list(query))
def organisations(self): """ Return a qs of organisations, with the most frequently related first. Each organisation is also annotated with 'position_count' which might be useful. This is intended as an alternative to assigning a org to each position_title. Instead we can deduce it from the postions. """ orgs = (Organisation.objects.filter(position__title=self).annotate( position_count=models.Count('position')).order_by( '-position_count')) return orgs
def with_rank(self): ranked = HudPitData.objects.annotate( rank=models.Window(expression=Rank(), partition_by=[ models.F('datatype'), models.F('geography'), models.F('year') ], order_by=models.F('value').asc()), total=models.Window(expression=models.Count( ['datatype', 'geography', 'year']), partition_by=[ models.F('datatype'), models.F('geography'), models.F('year') ])) return ranked
def with_rank(self): ranked = JCHSData.objects.exclude(datapoint='United States').annotate( asc_rank=models.Window(expression=Rank(), partition_by=[ models.F('datatype'), models.F('source'), models.F('date') ], order_by=models.F('value').asc()), desc_rank=models.Window(expression=Rank(), partition_by=[ models.F('datatype'), models.F('source'), models.F('date') ], order_by=models.F('value').desc()), total=models.Window(expression=models.Count( ['datatype', 'source', 'date']), partition_by=[ models.F('datatype'), models.F('source'), models.F('date') ])) return ranked
def add_annotations(self, queryset): return queryset.annotate( study_count=models.Count('observationalstudy'))
def cluster_points(self, precision): """Cluster modeles by geohash value with provided `precision`.""" field = self._geohash_field() return self._annotate_geohash(precision).values(field).annotate( cluster_count=models.Count(field), )
def get_list(self): return self.get_visible().annotate( num_publicbodies=models.Count('publicbody'))
def get_category_list(self): count = models.Count('categorized_publicbodies') return (self.get_queryset().filter( depth=1, is_topic=True).order_by('name').annotate(num_publicbodies=count))
def get_topic_list(self): return (self.get_queryset().filter(is_topic=True).order_by( 'rank', 'name').annotate(num_publicbodies=models.Count('publicbodies')))