Python django.db.models.Exists() Examples

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 get(self, request) :
        if not request.user.is_authenticated:
            thing_list = Thing.objects.all()
        else:
            thing_list = Thing.objects.annotate(
                FAV_USER_ID=Exists(Fav.objects.filter(user=self.request.user,thing_id=OuterRef('id')))
                ).all()
        ctx = {'thing_list' : thing_list}
        return render(request, self.template_name, ctx)

# https://stackoverflow.com/questions/2314920/django-show-log-orm-sql-calls-from-python-shell
# pip install django-extensions
# ./manage.py shell_plus --print-sql

# Below this line, we see raw sql...   With great power comes great responsibility
# https://docs.djangoproject.com/en/3.0/topics/db/sql/

# A List view using raw SQL - super efficient 

def handle(self, *args, **options):

        qs = AutoRating.objects.filter(deadline__gt=timezone.now())
        qs = qs.select_related('party')
        qs = qs.prefetch_related('party__rating_set')
        for auto_rating in tqdm.tqdm(qs, desc='update auto rating'):
            party = auto_rating.party
            contests = Contest.objects.filter(**auto_rating.info['filter'])

            party_contests = party.rating_set.filter(contest_id=OuterRef('pk'))
            contests = contests.annotate(in_party=Exists(party_contests)).filter(in_party=False)

            for contest in contests:
                rating = Rating(party=party, contest=contest)
                rating.save() 

def handle(self, *args, **options):
        self.stdout.write(str(options))
        args = AttrDict(options)

        if args.resources:
            if len(args.resources) == 1:
                contests = Contest.objects.filter(resource__module__resource__host__iregex=args.resources[0])
            else:
                resources = [Resource.objects.get(host__iregex=r) for r in args.resources]
                contests = Contest.objects.filter(resource__module__resource__host__in=resources)
        else:
            has_module = Module.objects.filter(resource_id=OuterRef('resource__pk'))
            contests = Contest.objects.annotate(has_module=Exists(has_module)).filter(has_module=True)

        if args.only_new:
            has_statistics = Statistics.objects.filter(contest_id=OuterRef('pk'))
            contests = contests.annotate(has_statistics=Exists(has_statistics)).filter(has_statistics=False)

        if args.year:
            contests = contests.filter(start_time__year=args.year)

        self.parse_statistic(
            contests=contests,
            previous_days=args.days,
            limit=args.limit,
            with_check=not args.no_check_timing,
            stop_on_error=args.stop_on_error,
            random_order=args.random_order,
            no_update_results=args.no_update_results,
            freshness_days=args.freshness_days,
            title_regex=args.event,
            users=args.users,
            with_stats=not args.no_stats,
            update_without_new_rating=args.update_without_new_rating,
        ) 

def queryset(self, request, queryset):
        if self.value() in ['yes', 'no']:
            logins = Login.objects.filter(team=OuterRef('pk'), stage=OuterRef('status'))
            queryset = queryset.annotate(has_login=Exists(logins))
            queryset = queryset.filter(has_login=self.value() == 'yes')
        return queryset 

def get_queryset(self):
        proj = self.get_content_object()
        related_content_objs = ContentObject.objects.filter(
            resource=OuterRef('id'),
            content_type=ContentType.objects.get_for_model(proj),
            object_id=proj.id)
        return super().get_queryset().prefetch_related(
            'content_objects__content_type').annotate(
            attached=Exists(related_content_objs)) 

def prepare_patches(request, m, max_depth=None):
    if m.total_patches == 1:
        return []
    replies = m.get_replies().filter(is_patch=True)
    commit_replies = api.models.Message.objects.filter(
        in_reply_to=OuterRef("message_id")
    )
    replies = replies.annotate(has_replies=Exists(commit_replies))
    project = m.project
    return [prepare_message(request, project, x, True) for x in replies] 

def _get_series_for_diff(self, q):
        def _get_message_data(m):
            filtered = ""
            sep = ""
            for l in m.get_body().splitlines():
                for pat, repl in [
                    (r"^index [0-9a-f]+\.\.[0-9a-f]+", r"index XXXXXXX..XXXXXXX"),
                    (
                        r"^@@ -[0-9]+,[0-9]+ \+[0-9]+,[0-9]+ @@( |$)",
                        r"@@ -XXX,XX +XXX,XX @@\1",
                    ),
                ]:
                    l = re.sub(pat, repl, l)
                filtered += sep + l
                sep = "\n"

            return PatchInfo(
                subject=m.subject,
                link=m.get_message_view_url(),
                has_replies=m.has_replies,
                body=filtered,
            )

        def _add_has_replies(q, **kwargs):
            replies = Message.objects.filter(
                in_reply_to=OuterRef("message_id"), **kwargs
            )
            return q.annotate(has_replies=Exists(replies))

        q = _add_has_replies(q, is_patch=False)
        s = q.first()

        ret = list()
        data = _get_message_data(s)
        ret.append(data)
        if not s.is_patch:
            for p in _add_has_replies(s.get_patches()):
                data = _get_message_data(p)
                ret.append(data)
        return ret 

def _with_has_successful_render_annotation(self):
        renders = Render.objects.filter(
            paper=models.OuterRef("pk"), state=Render.STATE_SUCCESS
        )
        return self.annotate(has_successful_render=models.Exists(renders)) 

def _with_has_not_deleted_render_annotation(self):
        renders = Render.objects.filter(paper=models.OuterRef("pk"), is_deleted=False)
        return self.annotate(has_not_deleted_render=models.Exists(renders)) 

def deployed(self):
        """
        Filter for deployed assets (i.e. assets having at least one deployed
        version) in an efficient way that doesn't involve joining or counting.
        https://docs.djangoproject.com/en/2.2/ref/models/expressions/#django.db.models.Exists
        """
        deployed_versions = AssetVersion.objects.filter(
            asset=OuterRef('pk'), deployed=True
        )
        return self.annotate(deployed=Exists(deployed_versions)).filter(
            deployed=True
        ) 

def test_union_with_values_list_on_annotated_and_unannotated(self):
        ReservedName.objects.create(name='rn1', order=1)
        qs1 = Number.objects.annotate(
            has_reserved_name=Exists(ReservedName.objects.filter(order=OuterRef('num')))
        ).filter(has_reserved_name=True)
        qs2 = Number.objects.filter(num=9)
        self.assertCountEqual(qs1.union(qs2).values_list('num', flat=True), [1, 9]) 

def test_union_with_values_list_on_annotated_and_unannotated(self):
        ReservedName.objects.create(name='rn1', order=1)
        qs1 = Number.objects.annotate(
            has_reserved_name=Exists(ReservedName.objects.filter(order=OuterRef('num')))
        ).filter(has_reserved_name=True)
        qs2 = Number.objects.filter(num=9)
        self.assertCountEqual(qs1.union(qs2).values_list('num', flat=True), [1, 9]) 

def standings_list(request, template='standings_list.html', extra_context=None):
    contests = Contest.objects \
        .select_related('timing') \
        .annotate(has_statistics=Exists(Statistics.objects.filter(contest=OuterRef('pk')))) \
        .annotate(has_module=Exists(Module.objects.filter(resource=OuterRef('resource_id')))) \
        .filter(Q(has_statistics=True) | Q(end_time__lte=timezone.now())) \
        .order_by('-end_time')

    has_perm_reset_statistic_timing = False
    all_standings = False
    if request.user.is_authenticated:
        all_standings = request.user.coder.settings.get('all_standings')
        has_perm_reset_statistic_timing = request.user.has_perm('reset_contest_statistic_timing')

    switch = 'switch' in request.GET
    if bool(all_standings) == bool(switch):
        contests = contests.filter(has_statistics=True, has_module=True)
        if request.user.is_authenticated:
            contests = contests.filter(request.user.coder.get_contest_filter(['list']))

    search = request.GET.get('search')
    if search is not None:
        contests = contests.filter(get_iregex_filter(search,
                                                     'title', 'host', 'resource__host',
                                                     mapping={
                                                         'slug': {'fields': ['slug']},
                                                         'writer': {'fields': ['info__writers__contains']},
                                                         'medal': {'fields': ['info__standings__medals'],
                                                                   'suff': '__isnull',
                                                                   'func': lambda v: False},
                                                     },
                                                     logger=request.logger))

    context = {
        'contests': contests,
        'timezone': get_timezone(request),
        'timeformat': get_timeformat(request),
        'all_standings': all_standings,
        'has_perm_reset_statistic_timing': has_perm_reset_statistic_timing,
        'switch': switch,
    }

    if extra_context is not None:
        context.update(extra_context)

    return render(request, template, context) 

def generate_course_certificates_for_fa_students():
    """
    Creates any missing unique course-user FACourseCertificates
    """
    courses = Course.objects.filter(
        program__live=True,
        program__financial_aid_availability=True
    )
    for course in courses:
        if not course.has_frozen_runs():
            continue

        course_certificates = MicromastersCourseCertificate.objects.filter(
            course=course,
            user=OuterRef('user')
        )
        # Find users that passed the course but don't have a certificate yet
        users_need_cert = FinalGrade.objects.annotate(
            course_certificate=Exists(course_certificates)
        ).filter(
            course_run__course=course,
            status=FinalGradeStatus.COMPLETE,
            passed=True,
            course_certificate=False
        ).values_list('user', flat=True)

        if course.has_exam:
            # need also to pass exam
            users_need_cert = ProctoredExamGrade.objects.filter(
                course=course,
                passed=True,
                exam_run__date_grades_available__lte=now_in_utc(),
                user__in=users_need_cert
            ).values_list('user', flat=True)

        for user in users_need_cert:
            try:
                MicromastersCourseCertificate.objects.get_or_create(
                    user_id=user,
                    course=course
                )
            except (IntegrityError, MicromastersCourseCertificate.DoesNotExist):
                log.exception(
                    "Unable to fetch or create certificate for user id: %d and course: %s",
                    user,
                    course.title
                )