def get_dataverse_counts_by_month(self, date_param=DVOBJECT_CREATEDATE_ATTR, **extra_filters): """ Return Dataverse counts by month """ # Was an error found earlier? # if self.was_error_found(): return self.get_error_msg_return() # ----------------------------------- # (1) Build query filters # ----------------------------------- # Exclude records where dates are null # - e.g. a record may not have a publication date exclude_params = {'%s__isnull' % date_param: True} if self.include_harvested: exclude_params[ 'dvobject__id__in'] = self.get_harvested_dataverse_ids() # Retrieve the date parameters # filter_params = self.get_date_filter_params() # Add extra filters from kwargs # if extra_filters: for k, v in extra_filters.items(): filter_params[k] = v # ----------------------------------- # (2) Construct query # ----------------------------------- # add exclude filters date filters # dv_counts_by_month = Dataverse.objects.select_related('dvobject'\ ).exclude(**exclude_params\ ).filter(**filter_params) # annotate query adding "month_year" and "count" # dv_counts_by_month = dv_counts_by_month.annotate(\ yyyy_mm=TruncYearMonth('%s' % date_param)\ ).values('yyyy_mm'\ ).annotate(count=models.Count('dvobject_id')\ ).values('yyyy_mm', 'count'\ ).order_by('%syyyy_mm' % self.time_sort) # ----------------------------------- # (2a) Get SQL query string # ----------------------------------- sql_query = str(dv_counts_by_month.query) # ----------------------------------- # (3) Format results # ----------------------------------- # hold the running total count running_total = self.get_dataverse_count_start_point(**extra_filters) formatted_records = [] # move from a queryset to a [] for d in dv_counts_by_month: rec_fmt = OrderedDict() # change the datetime object to a string rec_fmt['yyyy_mm'] = d['yyyy_mm'].strftime('%Y-%m') rec_fmt['count'] = d['count'] # running total running_total += d['count'] rec_fmt['running_total'] = running_total # d['month_year'] = d['yyyy_mm'].strftime('%Y-%m') # Add year and month numbers rec_fmt['year_num'] = d['yyyy_mm'].year rec_fmt['month_num'] = d['yyyy_mm'].month # Add month name month_name_found, month_name_short = get_month_name_abbreviation( d['yyyy_mm'].month) if month_name_found: assume_month_name_found, rec_fmt[ 'month_name'] = get_month_name(d['yyyy_mm'].month) rec_fmt['month_name_short'] = month_name_short else: # Log it!!!!!! pass # Add formatted record formatted_records.append(rec_fmt) data_dict = OrderedDict() data_dict['record_count'] = len(formatted_records) data_dict['total_count'] = running_total data_dict['records'] = formatted_records return StatsResult.build_success_result(data_dict, sql_query)
def get_dataverse_counts_by_type(self, exclude_uncategorized=True, **extra_filters): """ Return dataverse counts by 'dataversetype' Optional if a dataverse is uncategorized: - Specifying 'uncategorized_replacement_name' will set "UNCATEGORIZED" to another string Returns: { "dv_counts_by_type": [ { "dataversetype": "RESEARCH_PROJECTS", "type_count": 85, "total_count": 356, "percent_string": "23.9%" }, { "dataversetype": "TEACHING_COURSES", "type_count": 10, "total_count": 356, "percent_string": "2.8%" } ... etc ] } """ if self.was_error_found(): return self.get_error_msg_return() # Retrieve the date parameters # filter_params = self.get_date_filter_params(DVOBJECT_CREATEDATE_ATTR) # Add extra filters if extra_filters: for k, v in extra_filters.items(): filter_params[k] = v if exclude_uncategorized: exclude_params = dict(dataversetype=DATAVERSE_TYPE_UNCATEGORIZED) else: exclude_params = {} dataverse_counts_by_type = Dataverse.objects.select_related('dvobject'\ ).filter(**filter_params\ ).exclude(**exclude_params\ ).values('dataversetype'\ ).order_by('dataversetype'\ ).annotate(type_count=models.Count('dataversetype')\ ).order_by('-type_count') # ----------------------------------- # Get SQL query string # ----------------------------------- sql_query = str(dataverse_counts_by_type.query) # Count all dataverses # total_count = sum( [rec.get('type_count', 0) for rec in dataverse_counts_by_type]) total_count = total_count + 0.0 # Format the records, adding 'total_count' and 'percent_string' to each one # formatted_records = [] for rec in dataverse_counts_by_type: fmt_dict = OrderedDict() fmt_dict['dataversetype'] = rec['dataversetype'] fmt_dict['dataversetype_label'] = rec['dataversetype'].replace( '_', ' ') fmt_dict['type_count'] = rec.get('type_count', 0) if total_count > 0: float_percent = rec.get('type_count', 0) / total_count fmt_dict['total_count'] = int(total_count) fmt_dict['percent_string'] = '{0:.1%}'.format(float_percent) else: fmt_dict['total_count'] = 0 fmt_dict['percent_string'] = '0%' formatted_records.append(fmt_dict) data_dict = OrderedDict() data_dict['record_count'] = len(formatted_records) data_dict['records'] = formatted_records return StatsResult.build_success_result(data_dict, sql_query)
def get_topic_list(self): return (self.get_queryset().filter(is_topic=True) .order_by('rank', 'name') .annotate(num_publicbodies=models.Count('publicbodies')) )
def get_list(self): return self.get_visible().annotate(num_publicbodies=models.Count('publicbody'))
def get_counts(self, guid): works = dict( CompatReport.objects.filter( guid=guid).values_list('works_properly').annotate( models.Count('id'))) return {'success': works.get(True, 0), 'failure': works.get(False, 0)}
def get_models_for_count(*model_names): return [models.Count(model_name) for model_name in model_names]
def with_tracks_count(self): return self.annotate(_tracks_count=models.Count("tracks"))
def resolve_films(root, info, **kwargs): qs = Film.objects.prefetch_related("reporters") return qs.annotate(reporters_count=models.Count("reporters"))
def get_queryset(self): return super(NoStatementManager, self).get_queryset()\ .annotate(scount=models.Count('statement'))\ .exclude(scount__gt=0)
def with_albums_count(self): return self.annotate(_albums_count=models.Count("albums"))
def get_queryset(self, request): return super().get_queryset(request) \ .annotate(projects_count=models.Count('projects'))
def _filter_or_exclude(self, negate, *args, **kwargs): """ Custom lookups for tag fields """ if django.VERSION >= (3, 2): # Arguments changed to _filter_or_exclude(self, negate, args, kwargs) if len(args) == 2: args, kwargs = args elif set(kwargs.keys()) == {"args", "kwargs"}: args, kwargs = kwargs["args"], kwargs["kwargs"] # TODO: Replace with custom lookups safe_fields, singletag_fields, tag_fields, field_lookup = _split_kwargs( self.model, kwargs, lookups=True, with_fields=True) # Look up string values for SingleTagFields by name for field_name, val in singletag_fields.items(): query_field_name = field_name if isinstance(val, str): query_field_name += "__name" if not field_lookup[field_name].tag_options.case_sensitive: query_field_name += "__iexact" safe_fields[query_field_name] = val # Query as normal if django.VERSION >= (3, 2): qs = super(TaggedQuerySet, self)._filter_or_exclude(negate, args, safe_fields) else: qs = super(TaggedQuerySet, self)._filter_or_exclude(negate, *args, **safe_fields) # Look up TagFields by string name # # Each of these comparisons will be done with a subquery; for # A filter can chain, ie .filter(tags__name=..).filter(tags__name=..), # but exclude won't work that way; has to be done with a subquery for field_name, val in tag_fields.items(): val, lookup = val tag_options = field_lookup[field_name].tag_options # Only perform custom lookup if value is a string if not isinstance(val, str): if django.VERSION >= (3, 2): qs = super(TaggedQuerySet, self)._filter_or_exclude( negate, [], {field_name: val}) else: qs = super(TaggedQuerySet, self)._filter_or_exclude( negate, **{field_name: val}) continue # Parse the tag string tags = utils.parse_tags( val, space_delimiter=tag_options.space_delimiter) # Prep the subquery subqs = qs if negate: subqs = self.__class__(model=self.model, using=self._db) # To get an exact match, filter this queryset to only include # items with a tag count that matches the number of specified tags if lookup == "exact": count_name = "_tagulous_count_%s" % field_name subqs = subqs.annotate(**{ count_name: models.Count(field_name) }).filter(**{count_name: len(tags)}) # Explicit order as meta ordering will be ignored subqs = subqs.order_by("name") # Prep the field name query_field_name = field_name + "__name" if not tag_options.case_sensitive: query_field_name += "__iexact" # Now chain the filters for each tag # # Have to do it this way to create new inner joins for each tag; # ANDing Q objects will do it all on a single inner join, which # will match nothing for tag in tags: subqs = subqs.filter(**{query_field_name: tag}) # Fold subquery back into main query if negate: # Exclude on matched ID qs = qs.exclude(pk__in=subqs.values("pk")) else: # A filter op can just replace the main query qs = subqs return qs
def _folder(self, related, filters, option=None, order_by=None): """Base code, in common to the folders.""" qs = self.all() if option == OPTION_MESSAGES else QuerySet( self.model, PostmanQuery(self.model), using=self._db) if related: qs = qs.select_related(*related) if order_by: qs = qs.order_by(order_by) if isinstance(filters, (list, tuple)): lookups = models.Q() for filter in filters: lookups |= models.Q(**filter) else: lookups = models.Q(**filters) if option == OPTION_MESSAGES: return qs.filter(lookups) # Adding a 'count' attribute, to be similar to the by-conversation case, # should not be necessary. Otherwise add: # .extra(select={'count': 'SELECT 1'}) else: qs = qs.extra( select={'count': '{0}.count'.format(qs.query.pm_alias_prefix)}) qs.query.pm_set_extra(table=( # extra columns are always first in the SELECT query # for tests with the version 2.4.1 of sqlite3 in py26, add to the select: , 'id': 'postman_message.id' self.filter(lookups, thread_id__isnull=True).extra(select={'count': 0})\ .values_list('id', 'count').order_by(), # use separate annotate() to keep control of the necessary order self.filter(lookups, thread_id__isnull=False).values('thread').annotate(count=models.Count('pk')).annotate(id=models.Max('pk'))\ .values_list('id', 'count').order_by(), )) return qs
def task_mode_report(self): """ Returns queryset with annotate how many tasks with each task mode was """ return self.values("mode").annotate(task_count=models.Count("pk")).order_by("mode")
def count_each_kind(self): result = self.values_list("kind").annotate(count=models.Count("kind")) return dict(result)