def _remove_rcce_tag(Project): """ Remove RCCE tag and change it to combination of CEA and HEALTH_PUBLIC """ # Hardcode enum value present this time RCCE = 14 HEALTH_PUBLIC = 4 CEA = 2 # Add HEALTH_PUBLIC/CEA if there is RCCE for tag in [HEALTH_PUBLIC, CEA]: Project.objects\ .filter(secondary_sectors__contains=[RCCE])\ .exclude(secondary_sectors__contains=[tag])\ .update( secondary_sectors=Func( F('secondary_sectors'), Value(tag), function='ARRAY_APPEND', ) ) # Finally remove Project.objects.filter(secondary_sectors__contains=[RCCE]).update( secondary_sectors=Func( F('secondary_sectors'), Value(RCCE), function='ARRAY_REMOVE'))
def get_context_data(self, **kwargs): context = super().get_context_data(**kwargs) params = self.request.GET.copy() now = datetime.now() params.setdefault('hour', now.hour) params.setdefault('weekday', now.weekday()) params.setdefault('lng', -73.989774) params.setdefault('lat', 40.752336) hour = params.get('hour') weekday = params.get('weekday') lat = float(params.get('lat')) lng = float(params.get('lng')) point = Point(lng, lat, srid=4326) recommendations = (Recommendation.objects.filter( hour=hour, weekday=weekday).annotate( distance=Distance('poly', point)).annotate(point=Func( F('poly'), Func(Value(str(point)), function='ST_PointFromText'), function='ST_ClosestPoint')).filter( distance__lte=measure.Distance( mi=5).m)).order_by('-score')[:10] context['recommendations'] = [list(r.point) for r in recommendations] context['form'] = RecommendationForm(data=params) return context
def longrunning_filter(self, queryset, name, value): if value: return ( queryset.exclude(firefox_max_version__exact="") .annotate( firefox_min_int=Cast( Func( F("firefox_min_version"), Value(ExperimentConstants.VERSION_REGEX.pattern), function="substring", ), IntegerField(), ), firefox_max_int=Cast( Func( F("firefox_max_version"), Value(ExperimentConstants.VERSION_REGEX.pattern), function="substring", ), IntegerField(), ), version_count=F("firefox_max_int") - F("firefox_min_int"), ) .filter(version_count__gte=3) ) return queryset
def get_context_data(self, **kwargs): context = super(InvoiceEntriesDetail, self).get_context_data(**kwargs) billable_entries = context['billable_entries'] nonbillable_entries = context['nonbillable_entries'] context.update({ 'billable_total': billable_entries.aggregate(hours=Sum( Func(F('hours'), Value(2), function='ROUND')))['hours'], 'nonbillable_total': nonbillable_entries.aggregate(hours=Sum( Func(F('hours'), Value(2), function='ROUND')))['hours'], }) return context
def hitung_auditi_inspektorat(self, inspektorat): tgl_sekarang = datetime.now() data_auditi = list() bulan_sekarang = tgl_sekarang.month tahun_sekarang = tgl_sekarang.year jml_auditi = super()\ .filter(assign_jenis_kegiatan=1, assignment_st__assign_surat_id_assign__assignment_auditi__assign_auditee_id_auditee_id__gt='', assignment_st__assign_surat_id_assign__assignment_auditi__assign_auditee_id_auditee__auditee_inspektorat=inspektorat.value)\ .annotate(bulan=Func(Func(F('assignment_st__assign_surat_tgl'), function='FROM_UNIXTIME'), function='MONTH'), tahun=Func(Func(F('assignment_st__assign_surat_tgl'), function='FROM_UNIXTIME'), function='YEAR'))\ .values('bulan') if bulan_sekarang < 12: jml_auditi_st_sebelumnya = jml_auditi.filter(tahun=tahun_sekarang - 1)\ .annotate(total=Count('bulan'))\ .order_by('bulan') auditi_from_model = dict() for data in jml_auditi_st_sebelumnya: bulan = data.get('bulan') auditi_from_model.update({ bulan: data.get('total'), }) for i in range(bulan_sekarang + 1, 13): if auditi_from_model.get(i) is not None: data_auditi.append(auditi_from_model.get(i)) else: data_auditi.append(0) jml_auditi_st = jml_auditi.filter(tahun=tahun_sekarang) \ .annotate(total=Count('bulan')).order_by('bulan') auditi_from_model = dict() for data in jml_auditi_st: bulan = data.get('bulan') auditi_from_model.update({ bulan: data.get('total'), }) for i in range(1, bulan_sekarang + 1): if auditi_from_model.get(i) is not None: data_auditi.append(auditi_from_model.get(i)) else: data_auditi.append(0) return data_auditi
def resolve_expression( self, query=None, allow_joins=True, reuse=None, summarize=False, for_save=False, ): expressions = list(self.flatten()) # Split expressions and wrappers. index_expressions, wrappers = partition( lambda e: isinstance(e, self.wrapper_classes), expressions, ) wrapper_types = [type(wrapper) for wrapper in wrappers] if len(wrapper_types) != len(set(wrapper_types)): raise ValueError( "Multiple references to %s can't be used in an indexed " "expression." % ", ".join( [wrapper_cls.__qualname__ for wrapper_cls in self.wrapper_classes] ) ) if expressions[1 : len(wrappers) + 1] != wrappers: raise ValueError( "%s must be topmost expressions in an indexed expression." % ", ".join( [wrapper_cls.__qualname__ for wrapper_cls in self.wrapper_classes] ) ) # Wrap expressions in parentheses if they are not column references. root_expression = index_expressions[1] resolve_root_expression = root_expression.resolve_expression( query, allow_joins, reuse, summarize, for_save, ) if not isinstance(resolve_root_expression, Col): root_expression = Func(root_expression, template="(%(expressions)s)") if wrappers: # Order wrappers and set their expressions. wrappers = sorted( wrappers, key=lambda w: self.wrapper_classes.index(type(w)), ) wrappers = [wrapper.copy() for wrapper in wrappers] for i, wrapper in enumerate(wrappers[:-1]): wrapper.set_source_expressions([wrappers[i + 1]]) # Set the root expression on the deepest wrapper. wrappers[-1].set_source_expressions([root_expression]) self.set_source_expressions([wrappers[0]]) else: # Use the root expression, if there are no wrappers. self.set_source_expressions([root_expression]) return super().resolve_expression( query, allow_joins, reuse, summarize, for_save )
def _add_quality_relevance(self, qs): """Annotates query with relevance based on quality score. It is calculated by a formula: R = log(Q + 1) + 0.1 * v Where: R - Relevance; Q - Quality score (0 to 5); v - 1 if collection belongs to a partner namespace, otherwise 0. """ quality_rank_expr = ( Func(Coalesce(F('latest_version__quality_score'), 0) + 1, function='log') * CONTENT_SCORE_MULTIPLIER) vendor_rank_expr = Case( When(namespace__is_vendor=True, then=Value(VENDOR_RANK)), When(namespace__is_vendor=False, then=Value(0)), ) relevance_expr = F('quality_rank') + F('vendor_rank') return qs.annotate( quality_rank=Expr(quality_rank_expr, output_field=db_fields.FloatField()), vendor_rank=Expr(vendor_rank_expr, output_field=db_fields.FloatField()), relevance=Expr(relevance_expr, output_field=db_fields.FloatField()), )
def convert_context_to_csv(self, context): rows = [] rows.append([ 'Date', 'Weekday', 'Name', 'Location', 'Time In', 'Time Out', 'Breaks', 'Hours', ]) for entry in context['billable_entries']: data = [ entry.start_time.strftime('%x'), entry.start_time.strftime('%A'), entry.user.get_name_or_username(), entry.location, entry.start_time.strftime('%X'), entry.end_time.strftime('%X'), seconds_to_hours(entry.seconds_paused), "{0:.2f}".format(entry.hours), ] rows.append(data) total = context['billable_entries'].aggregate( hours=Sum(Func(F('hours'), Value(2), function='ROUND')))['hours'] rows.append( ('', '', '', '', '', '', 'Total:', "{0:.2f}".format(total))) return rows
def test_expressions(self): self.assertEqual( repr(Case(When(a=1))), "<Case: CASE WHEN <Q: (AND: ('a', 1))> THEN Value(None), ELSE Value(None)>" ) self.assertEqual(repr(Col('alias', 'field')), "Col(alias, field)") self.assertEqual(repr(Date('published', 'exact')), "Date(published, exact)") self.assertEqual(repr(DateTime('published', 'exact', utc)), "DateTime(published, exact, %s)" % utc) self.assertEqual(repr(F('published')), "F(published)") self.assertEqual(repr(F('cost') + F('tax')), "<CombinedExpression: F(cost) + F(tax)>") self.assertEqual( repr(ExpressionWrapper( F('cost') + F('tax'), models.IntegerField())), "ExpressionWrapper(F(cost) + F(tax))") self.assertEqual(repr(Func('published', function='TO_CHAR')), "Func(F(published), function=TO_CHAR)") self.assertEqual(repr(OrderBy(Value(1))), 'OrderBy(Value(1), descending=False)') self.assertEqual(repr(Random()), "Random()") self.assertEqual(repr(RawSQL('table.col', [])), "RawSQL(table.col, [])") self.assertEqual(repr(Ref('sum_cost', Sum('cost'))), "Ref(sum_cost, Sum(F(cost)))") self.assertEqual(repr(Value(1)), "Value(1)")
def _create_index_sql(self, model, *, fields=None, **kwargs): if fields is None or len(fields) != 1 or not hasattr( fields[0], 'geodetic'): return super()._create_index_sql(model, fields=fields, **kwargs) field = fields[0] expressions = None opclasses = None if field.geom_type == 'RASTER': # For raster fields, wrap index creation SQL statement with ST_ConvexHull. # Indexes on raster columns are based on the convex hull of the raster. expressions = Func(Col(None, field), template=self.rast_index_template) fields = None elif field.dim > 2 and not field.geography: # Use "nd" ops which are fast on multidimensional cases opclasses = [self.geom_index_ops_nd] name = kwargs.get('name') if not name: name = self._create_index_name(model._meta.db_table, [field.column], '_id') return super()._create_index_sql( model, fields=fields, name=name, using=' USING %s' % self.geom_index_type, opclasses=opclasses, expressions=expressions, )
def test_filter_conditional(self): query = Query(Item) where = query.build_where(Func(output_field=BooleanField())) exact = where.children[0] self.assertIsInstance(exact, Exact) self.assertIsInstance(exact.lhs, Func) self.assertIs(exact.rhs, True)
def _get_search_vector(self): """ Gets a `SearchVector` instance containing the rendered search document. :return: SearchVector """ fts_template = getattr(self._meta, 'fts_template', None) # make sure we have a template to generate the content of the FTS index if not fts_template: logger.warning( "'%(model)s' is FTSMixin instance but has no fts_template assigned." % {'model': self.__class__.__name__}) return # render the FTS document from the FTS template fts_template = get_template(fts_template) context = {'instance': self} fts_document = fts_template.render(context) return SearchVector(Func(Value(fts_document), function='unaccent', output_field=TextField()), config=self.fts_language)
def calculate_total(self, **units): """Calculate aggregated totals for the query. Args: units (dict): The units dictionary Returns: (dict) The aggregated totals for the query """ q_table = self._mapper.query_table query_group_by = ['date'] + self._get_group_by() query = q_table.objects.filter(self.query_filter) query_data = query.annotate(**self.annotations) query_data = query_data.values(*query_group_by) aggregates = self._mapper.report_type_map.get('aggregates') counts = None if 'count' in aggregates: resource_ids = query_data.annotate( resource_id=Func(F('resource_ids'), function='unnest') ).values_list('resource_id', flat=True).distinct() counts = len(resource_ids) total_query = query.aggregate(**aggregates) for unit_key, unit_value in units.items(): total_query[unit_key] = unit_value if counts: total_query['count'] = counts self._pack_data_object(total_query, **self._mapper.PACK_DEFINITIONS) return total_query
def next_id(code_class, contains=None): ''' Used in the Ajax request to help with creating the next id. :param code_class: the Collection_id code_class :param contains: the non numeric part of the id :return: returns a unique id conforming to the given rules ''' q1 = Collection_id.objects.filter(code_class=code_class) if code_class == 'Location ID' and contains: #should have a 'contains' with the pathogen abbreviation used on the -80 boxes. if Collection_id.objects.filter(code_txt__icontains=contains).exists(): q1 = q1.filter(code_txt__icontains=contains) pre = contains + '-' else: return '' elif code_class in ['NBC', 'NFC', 'NVC']: # this assumes the id to be an Naktuinbouw pathogen id pre = code_class + '-' #if code_class == 'NVC': # The virus from different locations have the same handel. # New entries should not to be in conflict with previous id's else: #should throw a error, other id return False q1 = q1.exclude(code_txt__isnull=True).exclude(code_txt__exact='') q1 = q1.annotate(code_int=Cast( Func(F('code_txt'), Value(r'[^\d]'), Value(''), Value('g'), function='regexp_replace'), IntegerField())) q1 = q1.order_by('-code_int').first() return pre + str(q1.code_int + 1)
def __init__(self, expression, **extra): """ Overridden to check for filtering star expressions. :param expression: The SQL function expression :type expression: django.db.models.expressions.Expression | str :param extra: Additional keyword arguments to pass on :type extra: any """ expression = Star() if expression == "*" else expression has_filter = "filter" in extra and extra["filter"] is not None if isinstance(expression, Star) and has_filter: raise ValueError("Star cannot be used with filter.") Func.__init__(self, expression, filter=filter, **extra)
def summaries(self, entries): # Get the list of bundle names and hour sums bundled_entries = entries.values('activity__activity_bundle', 'activity__activity_bundle__name') bundled_entries = bundled_entries.annotate( hours__sum=Sum(Func(F('hours'), Value(2), function='ROUND'))) bundled_entries = bundled_entries.order_by( 'activity__activity_bundle__order', 'activity__activity_bundle__name') bundled_totals = list( bundled_entries.values_list( 'activity__activity_bundle__name', 'activity__activity_bundle', 'hours__sum', )) # Get the list of activity names and hour sums activity_entries = entries.values('activity', 'activity__name', 'activity__activity_bundle') activity_entries = activity_entries.annotate( hours__sum=Sum(Func(F('hours'), Value(2), function='ROUND'))) activity_entries = activity_entries.order_by('activity') activity_totals = list( activity_entries.values_list( 'activity__name', 'activity__activity_bundle', 'hours__sum', )) totals = {} other_values = () for bundle in bundled_totals: bundle_key, bundle_value = bundle[0], bundle[2] act_values = [(act[0], act[2]) for act in activity_totals if act[1] == bundle[1]] if bundle_key is not None: totals[bundle_key] = (bundle_value, act_values) else: other_values = (bundle_value, act_values) totals = sorted(totals.items()) if other_values: totals.append(('Other', other_values)) all_totals = sum([bt[2] for bt in bundled_totals]) totals.append(('Total', (all_totals, []))) return totals
def as_oracle(self, compiler, connection, **extra_context): # Oracle prohibits mixing TextField (NCLOB) and CharField (NVARCHAR2), # so convert all fields to NCLOB when that type is expected. if self.output_field.get_internal_type() == 'TextField': clone = self.copy() clone.set_source_expressions([ Func(expression, function='TO_NCLOB') for expression in self.get_source_expressions() ]) return super(Coalesce, clone).as_sql(compiler, connection, **extra_context) return self.as_sql(compiler, connection, **extra_context)
def search(self, query): if not query: return self tsquery = Func(Value('russian'), Value(query), function='plainto_tsquery') return self.annotate( query_size_annotation=Func(tsquery, function='numnode', output_field=models.IntegerField()), found_annotation=Separator( '@@', Func(Value('russian'), F('content'), function='post_content_to_tsvector'), tsquery, output_field=models.BooleanField())).filter( Q(query_size_annotation=0) | Q(found_annotation=True))
def apply_sorting(self, order_lib): ''' Applies ordering on the columns and direction as given the order of importeance is determined by the number after the 'order' flag. :param order_lib: the columns to sort :return: changes self.data ''' order_lib = sorted([(int(k),i) for k,i in order_lib.items()]) order_list = [] for key,value in order_lib: sort_string = self.columns[value['column']]['data'] sort_list = [] if sort_string in ["id_collectie"]: str_name = sort_string+'_str' int_name = sort_string+'_int' self.data = self.data.annotate(**{ str_name:Func( F(sort_string), Value('[^A-Z]'), Value(''), Value('g'), function='regexp_replace') }) self.data = self.data.annotate(**{ int_name:Cast(Func( F(sort_string), Value('[^\d]'), Value(''), Value('g'), function='regexp_replace' ), IntegerField()) }) sort_list.append(str_name) sort_list.append(int_name) else: sort_list.append(sort_string) if value['dir'] == 'desc': for item in sort_list: order_list.append("-" + item) else: order_list += sort_list self.data = self.data.order_by(*order_list)
def _add_keyword_relevance(self, qs): """Annotates query with search rank value. Search rank is calculated as result of `ts_rank` PostgreSQL function, which ranks vectors based on the frequency of their matching lexemes. Search rank is normalized by dividing it by itself + 1: """ ts_rank_fn = Func(F('search_vector'), psql_search.SearchQuery(self.filters['keywords']), RANK_NORMALIZATION, function=RANK_FUNCTION, output_field=db_fields.FloatField()) return qs.annotate(relevance=ts_rank_fn)
def calculate_total(self, **units): """Calculate aggregated totals for the query. Args: units (dict): The units dictionary Returns: (dict) The aggregated totals for the query """ query_group_by = ["date"] + self._get_group_by() query = self.query_table.objects.filter(self.query_filter) query_data = query.annotate(**self.annotations) query_data = query_data.values(*query_group_by) aggregates = copy.deepcopy(self._mapper.report_type_map.get("aggregates", {})) if not self.parameters.parameters.get("compute_count"): # Query parameter indicates count should be removed from DB queries aggregates.pop("count", None) counts = None if "count" in aggregates: resource_ids = ( query_data.annotate(resource_id=Func(F("resource_ids"), function="unnest")) .values_list("resource_id", flat=True) .distinct() ) counts = len(resource_ids) total_query = query.aggregate(**aggregates) for unit_key, unit_value in units.items(): total_query[unit_key] = unit_value if counts: total_query["count"] = counts self._pack_data_object(total_query, **self._mapper.PACK_DEFINITIONS) return total_query
def _calculate_value_weights(user, question): """ Calculate the weights of the users metadata. :param user: The current user instance :type user: accounts.models.User :type question: The question that is answered :type question: activities.models.Question :return: A queryset with the average of the weights :rtype: django.db.models.query.QuerySet """ model = question.__class__ sub_q = model.objects.filter(id=question.id) sub_q = sub_q.value("weight") query = MetaData.objects.filter(Q(usermeta__user=user.id)) query = query.values("weight").union(sub_q) query = query.annotate(__result=Func("weight", function="AVG")) return query.values("__result")
def filter_by_q(self, queryset, name, value): """ Full text search provided by the 'q' option. Args: queryset: The query to add the additional full-text search filtering onto name: The name of the option specified, i.e. 'q' value: The string to search on Returns: The Django queryset that was passed in, additionally filtered by full-text search. """ search_query = SearchQuery(value) qs = queryset.filter(search_vector=search_query) ts_rank_fn = Func( F("search_vector"), search_query, 32, # RANK_NORMALIZATION = 32 function="ts_rank", output_field=db_fields.FloatField(), ) return qs.annotate(rank=ts_rank_fn).order_by("-rank")
def get_ids(self, obj): all_ids = (PdfStorage.objects.annotate( ids=Func(F("related_ids"), function="unnest")).values_list( "ids", flat=True).distinct()) return all_ids
def as_sql(self, *args, **kwargs): return Func.as_sql(self, *args, **kwargs)
def _add_relevance(self, qs): """Annotates query with relevance rank and its constituent values. Relevance is calculated by a formula: R = Sr + Dr + Qr, where R - relevance; Sr - search rank (from 0 to 1); Dr - download rank; Qr - quality rank; ts_rank() Sr = ------------- ts_rank() + 1 For more details on search rank see `_add_search_rank` function. Download rank is calculated by a formula: ln(cd + 1) Dr = 0.4 * -------------- ln(cd + 1) + 1 Quality rank is calculated by a formula: Qr = 0.2 * log(Q + 1) """ c = 'repository__community_score' d = 'repository__download_count' # ln((MOD*c + MIN) * d + 1) # where c = community_score and d = download_count # We're using the community_score as a modifier to the download count # instead of just allocating a certain number of points based on the # score. The reason for this is that the download score is # a logaritmic scale so adding a fixed number of points ended up # boosting scores way too much for content with low numbers of # downloads. This system allows for the weight of the community score # to scale with the number of downloads download_count_ln_expr = Func( (((Coalesce(F(c), 0) * COMMUNITY_SCORE_MODIFIER) + COMMUNITY_SCORE_MODIFIER_MIN) * F(d)) + 1, function='ln') download_rank_expr = (F('download_count_ln') / (1 + F('download_count_ln')) * DOWNLOAD_RANK_MULTIPLIER) q = 'repository__quality_score' # This function is better than using a linear function because it # makes it so that the effect of losing the first few points is # relatively minor, which reduces the impact of errors in scoring. quality_rank_expr = (Func(Coalesce(F(q), 0) + 1, function='log') * CONTENT_SCORE_MULTIPLIER) relevance_expr = (F('search_rank') + F('download_rank') + F('quality_rank')) return qs.annotate( download_count_ln=Expr(download_count_ln_expr, output_field=db_fields.FloatField()), download_rank=Expr(download_rank_expr, output_field=db_fields.FloatField()), quality_rank=Expr(quality_rank_expr, output_field=db_fields.FloatField()), relevance=Expr(relevance_expr, output_field=db_fields.FloatField()), )
def get_db_prep_save(self, value, connection): prepped_value = super(CompressedBinaryField, self).get_db_prep_save(value, connection) if connection.vendor == 'mysql': return Func(Value(prepped_value), function='COMPRESS') return prepped_value
def test_filter_conditional_join(self): query = Query(Item) filter_expr = Func('note__note', output_field=BooleanField()) msg = 'Joined field references are not permitted in this query' with self.assertRaisesMessage(FieldError, msg): query.build_where(filter_expr)
def test_filter_non_conditional(self): query = Query(Item) msg = 'Cannot filter against a non-conditional expression.' with self.assertRaisesMessage(TypeError, msg): query.build_where(Func(output_field=CharField()))