def __process_tags(self):
if self.model == MarkUnknown:
# Unknown marks don't have tags
return
v_model = self.__join_version()
sq_model = MarkSafeTag if self.model == MarkSafe else MarkUnsafeTag
tags_model = SafeTag if self.model == MarkSafe else UnsafeTag
subquery = RawQuery(sq_model)
subquery.select('mark_version_id')
subquery.join('INNER', tags_model, 'id', 'tag_id')
subquery.aggregate('tags', 'ARRAY_AGG({0})', ('tag', tags_model))
subquery.group_by('mark_version_id')
self.sql.join('LEFT OUTER',
subquery,
'mark_version_id',
'id',
table_to=v_model)
self.sql.select(('tags', subquery))
if 'tags' in self.view:
args = self.__get_tags(self.view['tags'][0])
operation = 'COUNT({0}) FILTER (WHERE {0} IN (%s))' % ','.join(
['%s'] * len(args))
subquery.aggregate('tags_filter_cnt',
operation,
'tag_id',
args_list=args)
self.sql.where('{0} = %s', ('tags_filter_cnt', subquery),
args_list=[len(args)])
def __process_total_similarity(self):
if 'total_similarity' not in self.view['columns']:
# We are not going to order by total similarity if it isn't shown
return
sq_model = {
ReportSafe: MarkSafeReport,
ReportUnsafe: MarkUnsafeReport,
ReportUnknown: MarkUnknownReport
}
# Subquery with marks numbers
links_sq = RawQuery(sq_model[self.model])
links_sq.select('report_id')
total_sim_aggr = '(CASE WHEN {0} IS NULL THEN 0.0 ELSE {0} END)'
links_sq.aggregate(
'total_similarity',
'(CASE WHEN COUNT({0}) = 0 THEN 0 ELSE (SUM({0})/COUNT({0})) END)',
'result')
links_sq.group_by('report_id')
self.sql.join('LEFT OUTER',
links_sq,
'report_id',
'id',
table_to=Report)
self.sql.aggregate('total_similarity', total_sim_aggr,
('total_similarity', links_sq))
if 'order' in self.view and self.view['order'][1] == 'total_similarity':
self.sql.order_by_aggregation(total_sim_aggr,
self._order_direction,
('total_similarity', links_sq))
def __process_problems(self):
if self.model != ReportUnknown:
return
# Get problems list for each report
if 'problems' in self.view['columns']:
subquery = RawQuery(MarkUnknownReport)
subquery.select('report_id')
subquery.join('INNER', UnknownProblem, 'id', 'problem_id')
subquery.aggregate('problems', 'ARRAY_AGG({0})',
('name', UnknownProblem))
subquery.group_by('report_id')
self.sql.join('LEFT OUTER',
subquery,
'report_id',
'id',
table_to=Report)
self.sql.select(('problems', subquery))
problem = self.kwargs.get('problem')
if problem is None and 'problem' in self.view:
try:
problem = UnknownProblem.objects.get(
name=self.view['problem'][0].strip())
except ObjectDoesNotExist:
raise EmptyQuery(
"Unknown problem wasn't found to filter by it")
if isinstance(problem, UnknownProblem):
subquery = RawQuery(MarkUnknownReport)
subquery.select('report_id')
subquery.where('{0} = %s', 'problem_id', args_list=[problem.id])
subquery.group_by('report_id')
self.sql.join('INNER',
subquery,
'report_id',
'id',
table_to=Report)
elif problem == 0:
subquery = RawQuery(MarkUnknownReport)
subquery.select('report_id')
subquery.group_by('report_id')
self.sql.join('LEFT OUTER',
subquery,
'report_id',
'id',
table_to=Report)
# Exclude all reports with marks
self.sql.where('{0} IS NULL', ('report_id', subquery))
def __process_tags(self):
if self.model == ReportUnknown:
# Unknowns don't have tags
return
report_tags_model = {
ReportSafe: SafeReportTag,
ReportUnsafe: UnsafeReportTag
}
tags_model = {ReportSafe: SafeTag, ReportUnsafe: UnsafeTag}
if 'tags' in self.view['columns']:
subquery = RawQuery(report_tags_model[self.model])
subquery.select('report_id')
subquery.join('INNER', tags_model[self.model], 'id', 'tag_id')
subquery.aggregate('tags', 'ARRAY_AGG({0})',
('tag', tags_model[self.model]))
subquery.group_by('report_id')
self.sql.join('LEFT OUTER',
subquery,
'report_id',
'id',
table_to=Report)
self.sql.select(('tags', subquery))
# Filter
operation = '{0} = %s'
if self.kwargs.get('tag') is not None:
args = [self.kwargs['tag'].id]
elif 'tags' in self.view:
args = self.__get_tags(self.view['tags'][0])
if len(args) > 1:
operation = '{0} IN (%s)' % ', '.join(['%s'] * len(args))
else:
# There are no filters by tag(s)
return
subquery = RawQuery(report_tags_model[self.model])
subquery.select('report_id')
subquery.where(operation, 'tag_id', args_list=args)
subquery.group_by('report_id')
self.sql.join('INNER', subquery, 'report_id', 'id', table_to=Report)
def __process_marks(self):
if 'marks_number' not in self.view['columns']:
# We are not going to fitler by number of (confirmed) marks if it isn't shown
return
# Is confirmed number shown?
with_confirmed = 'hidden' not in self.view or 'confirmed_marks' not in self.view[
'hidden']
sq_model = {
ReportSafe: MarkSafeReport,
ReportUnsafe: MarkUnsafeReport,
ReportUnknown: MarkUnknownReport
}
# Subquery with marks numbers
subquery = RawQuery(sq_model[self.model])
subquery.select('report_id')
subquery.aggregate('marks_number', 'COUNT({0})', 'id')
if with_confirmed:
subquery.aggregate(
'confirmed',
'COUNT(CASE WHEN {0} = %s THEN 1 ELSE NULL END)',
'type',
args_list=[ASSOCIATION_TYPE[1][0]])
subquery.group_by('report_id')
self.sql.join('LEFT OUTER',
subquery,
'report_id',
'id',
table_to=Report)
self.sql.aggregate('marks_number',
'(CASE WHEN {0} IS NULL THEN 0 ELSE {0} END)',
('marks_number', subquery))
if with_confirmed:
self.sql.aggregate('confirmed',
'(CASE WHEN {0} IS NULL THEN 0 ELSE {0} END)',
('confirmed', subquery))
# Filter
if 'marks_number' in self.view:
operation = '(CASE WHEN {0} IS NULL THEN 0 ELSE {0} END) '
if self.view['marks_number'][1] == 'lte':
operation += '<='
elif self.view['marks_number'][1] == 'gte':
operation += '>='
else: # iexact
operation += '='
operation += ' %s'
if self.view['marks_number'][0] == 'confirmed':
if not with_confirmed:
# Don't filter by confirmed number if it is hidden (wasn't aggregated)
return
field_name = 'confirmed'
else:
field_name = 'marks_number'
self.sql.where(operation, (field_name, subquery),
args_list=[int(self.view['marks_number'][2])])