Python django.db.models.Subquery() Examples

def test_orders_nulls_first_on_filtered_subquery(self):
        Article.objects.filter(headline='Article 1').update(author=self.author_1)
        Article.objects.filter(headline='Article 2').update(author=self.author_1)
        Article.objects.filter(headline='Article 4').update(author=self.author_2)
        Author.objects.filter(name__isnull=True).delete()
        author_3 = Author.objects.create(name='Name 3')
        article_subquery = Article.objects.filter(
            author=OuterRef('pk'),
            headline__icontains='Article',
        ).order_by().values('author').annotate(
            last_date=Max('pub_date'),
        ).values('last_date')
        self.assertQuerysetEqualReversible(
            Author.objects.annotate(
                last_date=Subquery(article_subquery, output_field=DateTimeField())
            ).order_by(
                F('last_date').asc(nulls_first=True)
            ).distinct(),
            [author_3, self.author_1, self.author_2],
        ) 

def _find_subqueries_in_where(children):
    for child in children:
        child_class = child.__class__
        if child_class is WhereNode:
            for grand_child in _find_subqueries_in_where(child.children):
                yield grand_child
        elif child_class is ExtraWhere:
            raise IsRawQuery
        elif child_class is NothingNode:
            pass
        else:
            rhs = child.rhs
            rhs_class = rhs.__class__
            if rhs_class is Query:
                yield rhs
            elif rhs_class is QuerySet:
                yield rhs.query
            elif rhs_class is Subquery or rhs_class is Exists:
                try:
                    yield rhs.query
                except:
                    yield rhs.queryset.query
            elif rhs_class in UNCACHABLE_FUNCS:
                raise UncachableQuery 

def test_orders_nulls_first_on_filtered_subquery(self):
        Article.objects.filter(headline='Article 1').update(author=self.author_1)
        Article.objects.filter(headline='Article 2').update(author=self.author_1)
        Article.objects.filter(headline='Article 4').update(author=self.author_2)
        Author.objects.filter(name__isnull=True).delete()
        author_3 = Author.objects.create(name='Name 3')
        article_subquery = Article.objects.filter(
            author=OuterRef('pk'),
            headline__icontains='Article',
        ).order_by().values('author').annotate(
            last_date=Max('pub_date'),
        ).values('last_date')
        self.assertQuerysetEqualReversible(
            Author.objects.annotate(
                last_date=Subquery(article_subquery, output_field=DateTimeField())
            ).order_by(
                F('last_date').asc(nulls_first=True)
            ).distinct(),
            [author_3, self.author_1, self.author_2],
        ) 

def all_latest(self) -> Dict[str, ModuleZipfile]:
        """
        Return all modules, unordered, indexed by ID.
        """
        # https://docs.djangoproject.com/en/2.2/ref/models/expressions/#subquery-expressions
        latest = Subquery(
            (
                DbModuleVersion.objects.filter(id_name=OuterRef("id_name"))
                .order_by("-last_update_time")
                .values("id")
            )[:1]
        )
        db_modules = list(
            DbModuleVersion.objects.annotate(_latest=latest).filter(id=F("_latest"))
        )

        ret = dict(StaticModuleLookup)  # fallback modules (TODO nix all static modules)
        ret.update({m.id_name: self._download_or_reuse_zipfile(m) for m in db_modules})
        return ret 

def threads(request):
    page = int(request.GET.get('p', 0))
    paging_size = settings.PAGING_SIZE
    tree = Comment.objects.filter( tree_id=OuterRef('tree_id'), user=OuterRef('user')).values('tree_id', 'user__pk').annotate(min_level=Min('level')).order_by()
    stories = Comment.objects.filter(
        user=request.user
    ).filter(
        Q(level__in=Subquery(tree.values('min_level'), output_field=models.IntegerField()))  # TODO: level= or level__in= ???
    ).select_related(
        'user', 'parent', 'to_story'
    ).order_by(
        '-created_at'
    )[(page*paging_size):(page+1)*(paging_size)]
    if len(stories) < 1 and page != 0:
        back = _one_page_back(request)
        if back:
            return back
    return render(request, 'news/index.html', {'stories': stories, 'hide_text':False, 'page': page, 'rank_start': None, 'show_children': True}) 

def get_queryset(self, term_id=None):
        term_usages = TermUsage.objects.all()

        self.read_request_filters()
        if hasattr(self, 'term_filter'):
            filter_val = self.term_filter[0]['value']
            term_usages = TermUsage.objects.filter(term__term__startswith=filter_val)
        term_usages = term_usages.order_by()

        qs = TextUnit.objects.only('pk', 'document_id', 'unit_type', 'language',
                                   'location_start', 'location_end').filter(
            pk__in=Subquery(term_usages.values('text_unit_id'))).order_by('document_id')
        filtered_projects = self.request.user.userprojectssavedfilter.projects.all()
        if filtered_projects:
            qs = qs.filter(document__project_id__in=filtered_projects)
        else:
            qs = self.filter_count_predicate(qs)
        return qs 

def filter_order_by_question_answer_value(queryset, _, question_slug):
        order_by = "-order_value" if question_slug.startswith("-") else "order_value"
        question_slug = question_slug.lstrip("-")

        # Based on question type, set answer field to use for sorting
        not_supported = (Question.TYPE_TABLE,)
        question = Question.objects.get(slug=question_slug)
        answer_value = "value"
        if question.type in not_supported:
            raise RuntimeError(
                f'Questions with type "{question.type}" are not supported '
                f'by "filterOrderByQuestionAnswerValue"'
            )
        elif question.type == Question.TYPE_DATE:
            answer_value = "date"
        elif question.type == Question.TYPE_FILE:
            answer_value = "file__name"

        # Initialize subquery
        answers = Answer.objects.filter(
            question=question, document=OuterRef("document")
        )

        # Annotate the cases in the queryset with the value of the answer of the given
        # question and order by it.
        return queryset.annotate(
            order_value=Subquery(answers.values(answer_value)[:1])
        ).order_by(order_by) 

def filter_owners_name(self, queryset, name, value):
        """Filter queryset by owner's name."""
        result = queryset.model.objects.none()
        user_subquery = self._get_user_subquery(value)
        for user in user_subquery:
            result = result.union(
                get_objects_for_user(
                    user, self.owner_permission, queryset, with_superuser=False
                )
            )

        # Union can no longer be filtered, so we have to create a new queryset
        # for following filters.
        return result.model.objects.filter(pk__in=Subquery(result.values("pk"))) 

def get_ordering_value(
        self, qs: QuerySet, value: Any
    ) -> Tuple[QuerySet, OrderingFieldType]:
        # First, join the requested answer, then annotate the QS accordingly.
        # Last, return a field corresponding to the value
        #
        question = Question.objects.get(pk=value)
        QUESTION_TYPE_TO_FIELD = {
            Question.TYPE_INTEGER: "value",
            Question.TYPE_FLOAT: "value",
            Question.TYPE_DATE: "date",
            Question.TYPE_CHOICE: "value",
            Question.TYPE_TEXTAREA: "value",
            Question.TYPE_TEXT: "value",
            Question.TYPE_FILE: "file",
            Question.TYPE_DYNAMIC_CHOICE: "value",
            Question.TYPE_STATIC: "value",
        }

        try:
            value_field = QUESTION_TYPE_TO_FIELD[question.type]
        except KeyError:  # pragma: no cover
            raise exceptions.ValidationError(
                f"Question '{question.slug}' has unsupported type {question.type} for ordering"
            )

        answers_subquery = Subquery(
            Answer.objects.filter(
                question=question,
                document=OuterRef(f"{self._document_locator_prefix}pk"),
            ).values(value_field)
        )
        ann_name = f"order_{value}"

        qs = qs.annotate(**{ann_name: answers_subquery})

        # TODO: respect document_via
        return qs, F(ann_name) 

def is_in_role(role_name, user_id, project_id):
    return RoleMapping.objects.filter(
        user_id=user_id,
        project_id=project_id,
        role_id=Subquery(Role.objects.filter(name=role_name).values('id')),
    ).exists() 

def populate_billing_log_invoice_from_proforma(apps, schema_editor):
    db_alias = schema_editor.connection.alias

    BillingLog = apps.get_model('silver', 'BillingLog')
    Proforma = apps.get_model('silver', 'Proforma')

    BillingLog.objects.using(db_alias) \
        .filter(invoice=None) \
        .update(
            invoice_id=Subquery(
                Proforma.objects.using(db_alias)
                .filter(id=OuterRef('proforma_id'))
                .values('related_document_id')[:1]
            )
        ) 

def populate_nickname(apps, schema_editor):
    from django.db.models import Subquery, OuterRef
    from ...core.conf import settings
    User = apps.get_model(settings.AUTH_USER_MODEL)
    UserProfile = apps.get_model("spirit_user", "UserProfile")
    first_user = UserProfile.objects.first()
    if first_user and not first_user.nickname:
        UserProfile.objects.all().update(
            nickname=Subquery(
                User.objects
                .filter(pk=OuterRef('user_id'))
                .values('username')[:1])) 

def stats(request):
    past_30_days = Render.objects.filter(
        created_at__gt=datetime.datetime.today() - datetime.timedelta(days=30)
    )

    newest_renders = Render.objects.filter(paper=OuterRef("pk")).order_by("-created_at")
    papers = Paper.objects.annotate(
        last_render_state=Subquery(newest_renders.values("state")[:1])
    ).exclude(last_render_state=None)

    return render(
        request,
        "papers/stats.html",
        {
            "total_renders": int(Render.objects.count()),
            "successful_renders": int(
                Render.objects.filter(state=Render.STATE_SUCCESS).count()
            ),
            "failed_renders": int(
                Render.objects.filter(state=Render.STATE_FAILURE).count()
            ),
            "total_renders_30_days": int(past_30_days.count()),
            "successful_renders_30_days": int(
                past_30_days.filter(state=Render.STATE_SUCCESS).count()
            ),
            "failed_renders_30_days": int(
                past_30_days.filter(state=Render.STATE_FAILURE).count()
            ),
            "total_papers": int(papers.count()),
            "successful_papers": int(
                papers.filter(last_render_state=Render.STATE_SUCCESS).count()
            ),
            "failed_papers": int(
                papers.filter(last_render_state=Render.STATE_FAILURE).count()
            ),
        },
    ) 

def get_user_data(cls, field: DocumentField,
                      project_ids: Optional[List[str]]) -> List[dict]:
        fd_repo = FieldDetectionRepository()
        qs_modified_document_ids = fd_repo.get_qs_active_modified_document_ids(field, project_ids)

        qs_finished_document_ids = fd_repo.get_qs_finished_document_ids(field.document_type, project_ids)
        return FieldAnnotation.objects.filter(Q(field=field),
                                              Q(text_unit__isnull=False),
                                              Q(document__in=Subquery(qs_modified_document_ids))
                                              | Q(document__in=Subquery(qs_finished_document_ids))) \
                   .values('modified_by', 'text_unit__textunittext__text', 'value', 'extraction_hint') \
                   .order_by('modified_by')[:settings.ML_TRAIN_DATA_SET_GROUP_LEN] 

def get_modified_field_ids(self,
                               documents: Union[QuerySet, List[Document]],
                               is_active: bool) -> QuerySet:
        return FieldValue.objects \
            .filter(document__in=Subquery(documents.values('pk').distinct('pk').order_by('pk')),
                    document__status__is_active=not is_active) \
            .values_list('field_id', flat=True) \
            .distinct('field_id').order_by('field_id') 

def update(self, instance: Document, validated_data):
        with transaction.atomic():
            system_fields_changed = list()

            new_status = validated_data.get('status')
            if new_status is not None and new_status.pk != instance.status_id:
                is_active = instance.status and instance.status.is_active
                if new_status.is_active != is_active:
                    field_ids = self.field_repo.get_doc_field_ids_with_values(instance.pk)
                    DocumentField.objects \
                        .filter(document_type_id=instance.document_type_id, pk__in=Subquery(field_ids)) \
                        .update(dirty=True)
                system_fields_changed.append(DocumentSystemField.status.value)

            user = self.context['request'].user  # type: User
            new_assignee = validated_data.get('assignee')
            prev_assignee = instance.assignee
            if new_assignee is None and prev_assignee is not None:
                validated_data['assign_date'] = None
                system_fields_changed.append(DocumentSystemField.assignee.value)
            elif new_assignee is not None and (prev_assignee is None or new_assignee.pk != prev_assignee.pk):
                validated_data['assign_date'] = datetime.datetime.now(tz=user.get_time_zone())
                system_fields_changed.append(DocumentSystemField.assignee.value)

            res = super().update(instance, validated_data)

            plan_process_document_changed(doc_id=instance.pk,
                                          system_fields_changed=system_fields_changed,
                                          generic_fields_changed=False,
                                          user_fields_changed=False,
                                          changed_by_user_id=user.pk)
            return res 

def qs_admins_and_managers(self) -> models.QuerySet:
        return self.filter(role__in=models.Subquery(Role.objects.qs_admins_or_managers().order_by().values_list('pk'))) 

def get_app_config_dump(document_type_codes=None) -> str:
    object_handler_by_model = {DocumentField: clear_owner}
    filter_by_model = {}
    if document_type_codes:
        def document_field_filter(qs):
            return qs.filter(document_type__code__in=document_type_codes)

        category_document_type_field = document_field_filter(DocumentField.objects.get_queryset()) \
            .values_list('category__pk') \
            .distinct('category__pk') \
            .order_by('category__pk')

        field_family_document_type_field = document_field_filter(DocumentField.objects.get_queryset()) \
            .values_list('family__pk') \
            .distinct('family__pk') \
            .order_by('family__pk')

        filter_by_model = dict(APP_CONFIG_MODELS)

        filter_by_model.update({
            DocumentType: lambda qs: qs.filter(code__in=document_type_codes),
            DocumentField: document_field_filter,
            DocumentFieldDetector: lambda qs: qs.filter(field__document_type__code__in=document_type_codes),
            DocumentFieldCategory: lambda qs: qs.filter(pk__in=Subquery(category_document_type_field)),
            DocumentFieldFamily: lambda qs: qs.filter(pk__in=Subquery(field_family_document_type_field))
        })
    return get_dump(filter_by_model, object_handler_by_model) 

def update_participation(self, participation):
        cumtime = 0
        score = 0
        format_data = {}

        queryset = (participation.submissions.values('problem_id')
                                             .filter(points=Subquery(
                                                 participation.submissions.filter(problem_id=OuterRef('problem_id'))
                                                                          .order_by('-points').values('points')[:1]))
                                             .annotate(time=Min('submission__date'))
                                             .values_list('problem_id', 'time', 'points'))

        for problem_id, time, points in queryset:
            if self.config['cumtime']:
                dt = (time - participation.start).total_seconds()
                if points:
                    cumtime += dt
            else:
                dt = 0

            format_data[str(problem_id)] = {'points': points, 'time': dt}
            score += points

        participation.cumtime = max(cumtime, 0)
        participation.score = score
        participation.tiebreaker = 0
        participation.format_data = format_data
        participation.save() 

def get_unfiltered_queryset(self):
        latest_rating_subquery = Rating.objects.filter(user=OuterRef('pk')).order_by('-contest__end_time')
        return (
            Profile.objects
            .filter(is_unlisted=False, user__is_active=True)
            .annotate(
                username=F('user__username'),
                latest_rating=Subquery(latest_rating_subquery.values('rating')[:1]),
                latest_volatility=Subquery(latest_rating_subquery.values('volatility')[:1]),
            )
            .order_by('id')
            .only('id', 'points', 'performance_points', 'problem_count', 'display_rank')
        ) 

def populate_labelling_msg_fields(apps, schema_editor):
    Labelling = apps.get_model("msgs", "Labelling")
    Message = apps.get_model("msgs", "Message")

    max_id = 0
    num_updated = 0
    while True:
        id_batch = list(
            Labelling.objects.filter(id__gt=max_id, message_created_on=None)
            .values_list("id", flat=True)
            .order_by("id")[:BATCH_SIZE]
        )
        if not id_batch:
            break

        Labelling.objects.filter(id__in=id_batch).update(
            message_is_flagged=Subquery(Message.objects.filter(id=OuterRef("message_id")).values("is_flagged")[:1]),
            message_is_archived=Subquery(Message.objects.filter(id=OuterRef("message_id")).values("is_archived")[:1]),
            message_created_on=Subquery(Message.objects.filter(id=OuterRef("message_id")).values("created_on")[:1]),
        )

        max_id = id_batch[-1]
        num_updated += len(id_batch)

        print(f" > Updated {num_updated} instances of labelling") 

def _get_tables(db_alias, query):
    if query.select_for_update or (
            not cachalot_settings.CACHALOT_CACHE_RANDOM
            and '?' in query.order_by):
        raise UncachableQuery

    try:
        if query.extra_select:
            raise IsRawQuery
        # Gets all tables already found by the ORM.
        tables = set(query.table_map)
        tables.add(query.get_meta().db_table)
        # Gets tables in subquery annotations.
        for annotation in query.annotations.values():
            if isinstance(annotation, Subquery):
                # Django 2.2+ removed queryset in favor of simply using query
                try:
                    tables.update(_get_tables(db_alias, annotation.queryset.query))
                except AttributeError:
                    tables.update(_get_tables(db_alias, annotation.query))
        # Gets tables in WHERE subqueries.
        for subquery in _find_subqueries_in_where(query.where.children):
            tables.update(_get_tables(db_alias, subquery))
        # Gets tables in HAVING subqueries.
        if isinstance(query, AggregateQuery):
            tables.update(
                _get_tables_from_sql(connections[db_alias], query.subquery))
        # Gets tables in combined queries
        # using `.union`, `.intersection`, or `difference`.
        if query.combined_queries:
            for combined_query in query.combined_queries:
                tables.update(_get_tables(db_alias, combined_query))
    except IsRawQuery:
        sql = query.get_compiler(db_alias).as_sql()[0].lower()
        tables = _get_tables_from_sql(connections[db_alias], sql)

    if not are_all_cachable(tables):
        raise UncachableQuery
    return tables 

def get_next_expression(self, model_instance):
        """ Generate an expression that will evaluate to the next valid ordering value """
        # This will be the next number larger than existing records in the ordering set
        # If no records in the ordering set, start from 0
        # Evade any custom model managers
        qs = models.QuerySet(self.model).filter(**self.get_filter_kwargs_for_object(model_instance))
        qs = qs.annotate(_next=Max(self.attname) + 1).values('_next').order_by()
        # Hackishly clip group_by clause to guarantee single result
        qs.query.group_by = []
        return BypassExpression(Coalesce(Subquery(qs), 0, output_field=models.IntegerField())) 

def get_queryset(self):
        from timed.employment.models import PublicHoliday

        queryset = super().get_queryset()
        queryset = queryset.exclude(
            date__in=models.Subquery(
                PublicHoliday.objects.filter(
                    location__employments__user=models.OuterRef("user")
                ).values("date")
            )
        )
        return queryset 

def get_queryset(self):
        qs = super().get_queryset()
        qs = qs.filter(value__isnull=False, document__delete_pending=False)

        # via project router
        project_id = self.kwargs.get('project_pk')
        if project_id:
            qs = qs.filter(document__project_id=project_id)

        field_annotation_subquery = FieldAnnotation.objects \
            .filter(field=OuterRef("field"), document=OuterRef("document")) \
            .order_by('field', 'document') \
            .values('field', 'document') \
            .annotate(ann=GroupConcat('location_text', '\n-----\n'))

        qs = qs.annotate(
            modified_by_username=F('modified_by__username'),
            project=F('document__project__name'),
            project_id=F('document__project_id'),
            document_name=F('document__name'),
            document_status=F('document__status__name'),
            field_name=Concat('field__document_type__title', Value(': '), 'field__title'))

        qs = qs.select_related('document', 'document__project', 'document__status',
                               'field', 'field__document_type', 'modified_by')

        if self.action == 'list':
            qs = qs.annotate(
                location_text=Subquery(field_annotation_subquery.values('ann')[:1],
                                       output_field=TextField()))

        qs = qs.only(
            'document_id', 'document__name', 'document__status__name',
            'document__project_id', 'document__project__name',
            'field_id', 'field__title', 'field__type',
            'field__document_type__title',
            'value',
            'modified_by_id', 'modified_by__username', 'modified_date')

        return qs


# --------------------------------------------------------
#  Field Annotation Status Views
# -------------------------------------------------------- 

def set_status(self, request, **kwargs):
        """
        Bulk set status for batch of documents\n
            Params:
                document_ids: list[int]
                no_document_ids: list[int] - exclude those docs from action (if "all" is set)
                all: any value - update all documents if any value
                status_id: int
            Returns:
                int (number of reassigned documents)
        """
        # permissions check
        project = self.get_object()
        status_id = request.data.get('status_id')

        if request.data.get('all'):
            documents = self.get_document_queryset()
            if request.data.get('no_document_ids'):
                documents = documents.exclude(pk__in=request.data.get('no_document_ids'))
            document_ids = documents.values_list('pk', flat=True)
        else:
            document_ids = request.data.get('document_ids')

        documents = Document.objects \
            .filter(project=project, pk__in=document_ids)

        import apps.document.repository.document_field_repository as dfr
        field_repo = dfr.DocumentFieldRepository()

        with transaction.atomic():
            review_status = ReviewStatus.objects.get(pk=status_id)
            modified_fields = field_repo.get_modified_field_ids(
                documents, review_status.is_active)
            DocumentField.objects.filter(
                pk__in=Subquery(modified_fields)).update(dirty=True)
            ret = documents.update(status=status_id)
            # TODO: do not hardcode doc status code
            if review_status.code in ('completed', 'excluded'):
                FieldAnnotation.objects.filter(document__in=documents).update(
                    status=FieldAnnotationStatus.accepted_status())

        plan_process_documents_status_changed(document_ids, status_id, request.user.pk)

        return Response({'success': ret}) 

def get_sorted_articles(self, published_only=True):
        """ Returns issue articles sorted by section and article order

        Many fields are prefetched and annotated to handle large issues more
        eficiently. In particular, it annotates relevant SectionOrder and
        ArticleOrdering rows as section_order and article_order respectively.
        Returns a Queryset which should keep the memory footprint at a minimum
        """

        section_order_subquery = SectionOrdering.objects.filter(
            section=OuterRef("section__pk"),
            issue=Value(self.pk),
        ).values_list("order")

        article_order_subquery = ArticleOrdering.objects.filter(
            section=OuterRef("section__pk"),
            article=OuterRef("pk"),
            issue=Value(self.pk),
        ).values_list("order")

        issue_articles = self.articles.prefetch_related(
            'authors',
            'frozenauthor_set',
            'manuscript_files',
        ).select_related(
            'section',
        ).annotate(
            section_order=Subquery(section_order_subquery),
            article_order=Subquery(article_order_subquery),
        ).order_by(
            "section_order",
            "section__sequence",
            "section__pk",
            "article_order",
        )

        if published_only:
            issue_articles = issue_articles.filter(
                stage=submission_models.STAGE_PUBLISHED,
                date_published__lte=timezone.now(),
            )

        return issue_articles 

def update_participation(self, participation):
        cumtime = 0
        score = 0
        format_data = {}

        submissions = participation.submissions.exclude(submission__result__in=('IE', 'CE'))

        submission_counts = {
            data['problem_id']: data['count'] for data in submissions.values('problem_id').annotate(count=Count('id'))
        }
        queryset = (
            submissions
            .values('problem_id')
            .filter(
                submission__date=Subquery(
                    submissions
                    .filter(problem_id=OuterRef('problem_id'))
                    .order_by('-submission__date')
                    .values('submission__date')[:1],
                ),
            )
            .annotate(points=Max('points'))
            .values_list('problem_id', 'problem__points', 'points', 'submission__date')
        )

        for problem_id, problem_points, points, date in queryset:
            sub_cnt = submission_counts.get(problem_id, 0)

            dt = (date - participation.start).total_seconds()

            bonus = 0
            if points > 0:
                # First AC bonus
                if sub_cnt == 1 and points == problem_points:
                    bonus += self.config['first_ac_bonus']
                # Time bonus
                if self.config['time_bonus']:
                    bonus += (participation.end_time - date).total_seconds() // 60 // self.config['time_bonus']

            format_data[str(problem_id)] = {'time': dt, 'points': points, 'bonus': bonus}

        for data in format_data.values():
            if self.config['cumtime']:
                cumtime += data['time']
            score += data['points'] + data['bonus']

        participation.cumtime = cumtime
        participation.score = score
        participation.tiebreaker = 0
        participation.format_data = format_data
        participation.save() 

def _get_string_attribute_values(self, uuids, string_pred_uuids):
        """Gets string attribute values from the database

        :param list uuids: List of UUIDs for the solr documents in the
            results that may have string attributes.
        :param list string_pred_uuids: List of string predicates from
            solr docs
        """

        if not len(string_pred_uuids):
            # Return an empty dict if there are no string predicates.
            return {}

        # NOTE: We need to query the database to get the string content
        # associated with string attribute predicates, because we do
        # not store this in solr.
        
        # This queryset will be in a SubQuery to effectively make a 
        # join between Assertions and OC strings via the 
        # assertion.object_uuid and ocstring.uuid keys.
        str_qs = OCstring.objects.filter(
            uuid=OuterRef('object_uuid')
        ).values('content')

        ass_qs = Assertion.objects.filter(
            uuid__in=uuids,
            predicate_uuid__in=string_pred_uuids,
        ).exclude(
            visibility__lt=1
        ).order_by(
            'uuid', 
            'predicate_uuid', 
            'sort'
        ).annotate(
            str_content=Subquery(
                str_qs
            )
        )
        output = {}
        for a in ass_qs:
            if not a.uuid in output:
                output[a.uuid] = {}
            if not a.predicate_uuid in output[a.uuid]:
                output[a.uuid][a.predicate_uuid] = []
            output[a.uuid][a.predicate_uuid].append(
                a.str_content
            )
        return output 

def api_v1_contest_detail(request, contest):
    contest = get_object_or_404(Contest, key=contest)

    if not contest.is_accessible_by(request.user):
        raise Http404()

    in_contest = contest.is_in_contest(request.user)
    can_see_rankings = contest.can_see_full_scoreboard(request.user)

    problems = list(contest.contest_problems.select_related('problem')
                    .defer('problem__description').order_by('order'))

    new_ratings_subquery = Rating.objects.filter(participation=OuterRef('pk'))
    old_ratings_subquery = (Rating.objects.filter(user=OuterRef('user__pk'),
                                                  contest__end_time__lt=OuterRef('contest__end_time'))
                            .order_by('-contest__end_time'))
    participations = (contest.users.filter(virtual=0, user__is_unlisted=False)
                      .annotate(new_rating=Subquery(new_ratings_subquery.values('rating')[:1]))
                      .annotate(old_rating=Subquery(old_ratings_subquery.values('rating')[:1]))
                      .prefetch_related('user__organizations')
                      .annotate(username=F('user__user__username'))
                      .order_by('-score', 'cumtime', 'tiebreaker') if can_see_rankings else [])
    can_see_problems = (in_contest or contest.ended or contest.is_editable_by(request.user))

    return JsonResponse({
        'time_limit': contest.time_limit and contest.time_limit.total_seconds(),
        'start_time': contest.start_time.isoformat(),
        'end_time': contest.end_time.isoformat(),
        'tags': list(contest.tags.values_list('name', flat=True)),
        'is_rated': contest.is_rated,
        'rate_all': contest.is_rated and contest.rate_all,
        'has_rating': contest.ratings.exists(),
        'rating_floor': contest.rating_floor,
        'rating_ceiling': contest.rating_ceiling,
        'format': {
            'name': contest.format_name,
            'config': contest.format_config,
        },
        'problems': [
            {
                'points': int(problem.points),
                'partial': problem.partial,
                'name': problem.problem.name,
                'code': problem.problem.code,
            } for problem in problems] if can_see_problems else [],
        'rankings': [
            {
                'user': participation.username,
                'points': participation.score,
                'cumtime': participation.cumtime,
                'tiebreaker': participation.tiebreaker,
                'old_rating': participation.old_rating,
                'new_rating': participation.new_rating,
                'is_disqualified': participation.is_disqualified,
                'solutions': contest.format.get_problem_breakdown(participation, problems),
            } for participation in participations],
    })