def filter_channel(self, queryset, name, value):
channel_queryset = Channel.objects.filter(pk=value)
queryset = queryset.annotate(
can_edit=Cast(
Cast(
SQCount(
channel_queryset.filter(editors=OuterRef("id")),
field="id",
),
IntegerField(),
),
BooleanField(),
),
can_view=Cast(
Cast(
SQCount(
channel_queryset.filter(viewers=OuterRef("id")),
field="id",
),
IntegerField(),
),
BooleanField(),
),
)
if self.request.GET.get("include_viewonly"):
return queryset.filter(Q(can_edit=True) | Q(can_view=True))
return queryset.filter(can_edit=True)
def complete_annotations(self, queryset):
queryset = queryset.annotate(**self.annotations)
editors_query = (User.objects.filter(
editable_channels__id=OuterRef("id")).values_list(
"id", flat=True).distinct())
viewers_query = (User.objects.filter(
view_only_channels__id=OuterRef("id")).values_list(
"id", flat=True).distinct())
nodes = With(
ContentNode.objects.values(
"id",
"tree_id").filter(tree_id__in=self.page_tree_ids).order_by(),
name="nodes",
)
file_query = (nodes.join(
File, contentnode_id=nodes.col.id).with_cte(nodes).filter(
contentnode__tree_id=OuterRef("main_tree__tree_id")).values(
"checksum", "file_size").distinct())
queryset = queryset.annotate(
editors_count=SQCount(editors_query, field="id"),
viewers_count=SQCount(viewers_query, field="id"),
size=SQSum(file_query, field="file_size"),
)
return queryset
def filter_subtitles(self, queryset, name, value):
subtitle_query = self.main_tree_query.filter(
files__preset__subtitle=True, kind_id=content_kinds.VIDEO
)
return queryset.annotate(
subtitle_count=SQCount(subtitle_query, field="content_id")
).exclude(subtitle_count=0)
def filter_licenses(self, queryset, name, value):
license_query = (self.main_tree_query.filter(
license_id__in=[int(l_id) for l_id in value.split(",")]).values(
"content_id").distinct())
return queryset.annotate(
license_count=SQCount(license_query, field="content_id")).exclude(
license_count=0)
def filter_kinds(self, queryset, name, value):
kinds_query = (
self.main_tree_query.filter(kind_id__in=value.split(","))
.values("content_id")
.distinct()
)
return queryset.annotate(
kind_match_count=SQCount(kinds_query, field="content_id")
).exclude(kind_match_count=0)
def annotate_queryset(self, queryset):
descendant_resources = ContentNode.objects.filter(
tree_id=OuterRef("tree_id"),
lft__gt=OuterRef("lft"),
rght__lt=OuterRef("rght"),
).exclude(kind_id=content_kinds.TOPIC)
return queryset.annotate(
total_count=(F("rght") - F("lft") - 1) / 2,
resource_count=SQCount(descendant_resources, field="content_id"),
)
def filter_chef(self, queryset, name, value):
chef_channel_query = (
Channel.objects.filter(editors__id=OuterRef("id"), deleted=False)
.exclude(ricecooker_version=None)
.values_list("id", flat=True)
.distinct()
)
return queryset.annotate(
chef_count=SQCount(chef_channel_query, field="id")
).filter(chef_count__gt=0)
def compose_annotations(self):
annotations = {}
annotations["editable_channels__ids"] = NotNullArrayAgg("editable_channels__id")
annotations["view_only_channels__ids"] = NotNullArrayAgg(
"view_only_channels__id"
)
annotations["name"] = Concat(
F("first_name"), Value(" "), F("last_name"), output_field=CharField()
)
edit_channel_query = (
Channel.objects.filter(editors__id=OuterRef("id"), deleted=False)
.values_list("id", flat=True)
.distinct()
)
viewonly_channel_query = (
Channel.objects.filter(viewers__id=OuterRef("id"), deleted=False)
.values_list("id", flat=True)
.distinct()
)
annotations["edit_count"] = SQCount(edit_channel_query, field="id")
annotations["view_count"] = SQCount(viewonly_channel_query, field="id")
return annotations
def complete_annotations(self, queryset):
queryset = queryset.annotate(primary_token=primary_token_subquery)
channel_main_tree_nodes = ContentNode.objects.filter(
tree_id=OuterRef("main_tree__tree_id"))
# Add the last modified node modified value as the channel last modified
queryset = queryset.annotate(modified=Subquery(
channel_main_tree_nodes.values("modified").order_by("-modified")
[:1]))
# Add the unique count of distinct non-topic node content_ids
non_topic_content_ids = (channel_main_tree_nodes.exclude(
kind_id=content_kinds.TOPIC).order_by("content_id").distinct(
"content_id").values_list("content_id", flat=True))
queryset = queryset.annotate(count=SQCount(non_topic_content_ids,
field="content_id"), )
return queryset
def complete_annotations(self, queryset):
thumbnails = File.objects.filter(
contentnode=OuterRef("id"), preset__thumbnail=True
)
descendant_resources = (
ContentNode.objects.filter(
tree_id=OuterRef("tree_id"),
lft__gt=OuterRef("lft"),
rght__lt=OuterRef("rght"),
)
.exclude(kind_id=content_kinds.TOPIC)
.values("id", "role_visibility", "changed")
.order_by()
)
content_id_query = self.get_accessible_nodes_queryset().filter(
content_id=OuterRef("content_id")
)
original_channel_name = Coalesce(
Subquery(
Channel.objects.filter(pk=OuterRef("original_channel_id")).values(
"name"
)[:1]
),
Subquery(
Channel.objects.filter(main_tree__tree_id=OuterRef("tree_id")).values(
"name"
)[:1]
),
)
queryset = queryset.annotate(
location_ids=SQArrayAgg(content_id_query, field="id"),
resource_count=SQCount(descendant_resources, field="id"),
thumbnail_checksum=Subquery(thumbnails.values("checksum")[:1]),
thumbnail_extension=Subquery(
thumbnails.values("file_format__extension")[:1]
),
content_tags=NotNullMapArrayAgg("tags__tag_name"),
original_channel_name=original_channel_name,
)
return queryset
def annotate_queryset(self, queryset):
queryset = queryset.annotate(total_count=(F("rght") - F("lft") - 1) /
2)
descendant_resources = (ContentNode.objects.filter(
tree_id=OuterRef("tree_id"),
lft__gt=OuterRef("lft"),
rght__lt=OuterRef("rght"),
).exclude(kind_id=content_kinds.TOPIC).values("id", "role_visibility",
"changed").order_by())
all_descendants = (ContentNode.objects.filter(
tree_id=OuterRef("tree_id"),
lft__gt=OuterRef("lft"),
rght__lt=OuterRef("rght"),
).values("id", "complete", "published").order_by())
# Get count of descendant nodes with errors
descendant_errors = all_descendants.filter(complete=False)
changed_descendants = descendant_resources.filter(changed=True)
thumbnails = File.objects.filter(contentnode=OuterRef("id"),
preset__thumbnail=True)
original_channel_name = Coalesce(
Subquery(
Channel.objects.filter(
pk=OuterRef("original_channel_id")).values("name")[:1]),
Subquery(
Channel.objects.filter(main_tree__tree_id=OuterRef(
"tree_id")).values("name")[:1]),
)
original_node = ContentNode.objects.filter(
node_id=OuterRef("original_source_node_id")).filter(
node_id=F("original_source_node_id"))
root_id = ContentNode.objects.filter(tree_id=OuterRef("tree_id"),
parent__isnull=True).values_list(
"id", flat=True)[:1]
assessment_items = (AssessmentItem.objects.filter(
contentnode_id=OuterRef("id"),
deleted=False).values_list("assessment_id", flat=True).distinct())
queryset = queryset.annotate(
resource_count=SQCount(descendant_resources, field="id"),
coach_count=SQCount(
descendant_resources.filter(role_visibility=roles.COACH),
field="id",
),
assessment_item_count=SQCount(assessment_items,
field="assessment_id"),
error_count=SQCount(descendant_errors, field="id"),
has_updated_descendants=Exists(
changed_descendants.filter(published=True).values("id")),
has_new_descendants=Exists(
changed_descendants.filter(published=False).values("id")),
thumbnail_checksum=Subquery(thumbnails.values("checksum")[:1]),
thumbnail_extension=Subquery(
thumbnails.values("file_format__extension")[:1]),
original_channel_name=original_channel_name,
original_parent_id=Subquery(original_node.values("parent_id")[:1]),
original_node_id=Subquery(original_node.values("pk")[:1]),
has_children=Exists(
ContentNode.objects.filter(
parent=OuterRef("id")).values("pk")),
root_id=Subquery(root_id),
)
queryset = queryset.annotate(
content_tags=NotNullMapArrayAgg("tags__tag_name"))
return queryset
def filter_assessments(self, queryset, name, value):
assessment_query = self.main_tree_query.filter(kind_id=content_kinds.EXERCISE)
return queryset.annotate(
assessment_count=SQCount(assessment_query, field="content_id")
).exclude(assessment_count=0)
def filter_coach(self, queryset, name, value):
coach_query = self.main_tree_query.filter(role_visibility=roles.COACH)
return queryset.annotate(
coach_count=SQCount(coach_query, field="content_id")
).exclude(coach_count=0)