Python django.db.models.F Examples

def notify_stock_limits(self):
        """
        Notifies the correct parties of inventory items stocking status
        """
        subject = _(u"Products stocked below limit")

        for l in Location.objects.filter(enabled=True):
            out_of_stock = Inventory.objects.filter(
                location=l,
                amount_stocked__lt=F('amount_minimum')
            )

            table = CsvTable()
            table.addheader(['PRODUCT', 'MINIMUM', 'STOCKED'])

            for i in out_of_stock:
                table.addrow([i.product.code, i.amount_minimum, i.amount_stocked])

            if Configuration.notify_location():
                recipient = l.manager.email if l.manager else l.email
                send_table(self.mail_sender, recipient, subject, table)
            if self.mail_recipient:
                send_table(self.mail_sender, self.mail_recipient, subject, table) 

def handle_rollover(inst: Instrument, new_bar: DailyBar):
    """
    换月处理, 基差=新合约收盘价-旧合约收盘价, 从今日起之前的所有连续合约的OHLC加上基差
    """
    product_code = re.findall('[A-Za-z]+', new_bar.code)[0]
    old_bar = DailyBar.objects.filter(exchange=inst.exchange, code=inst.last_main, time=new_bar.time).first()
    main_bar = MainBar.objects.get(
        exchange=inst.exchange, product_code=product_code, time=new_bar.time)
    if old_bar is None:
        old_close = new_bar.close
    else:
        old_close = old_bar.close
    basis = new_bar.close - old_close
    main_bar.basis = basis
    basis = float(basis)
    main_bar.save(update_fields=['basis'])
    MainBar.objects.filter(exchange=inst.exchange, product_code=product_code, time__lte=new_bar.time).update(
        open=F('open') + basis, high=F('high') + basis,
        low=F('low') + basis, close=F('close') + basis, settlement=F('settlement') + basis) 

def filter_queryset(self, request, queryset, view):
        ordering = self.get_ordering(request, queryset, view)

        if ordering:
            f_ordering = []
            for o in ordering:
                if not o:
                    continue
                if o[0] == "-":
                    f_ordering.append(F(o[1:]).desc(nulls_last=True))
                else:
                    f_ordering.append(F(o).asc(nulls_last=True))

            return queryset.order_by(*f_ordering)

        return queryset 

def annotations(self):
        """Create dictionary for query annotations.

        Returns:
            (Dict): query annotations dictionary

        """
        annotations = {"date": self.date_trunc("usage_start")}
        # { query_param: database_field_name }
        fields = self._mapper.provider_map.get("annotations")
        for q_param, db_field in fields.items():
            annotations[q_param] = F(db_field)
        if (
            "project" in self.parameters.parameters.get("group_by", {})
            or "and:project" in self.parameters.parameters.get("group_by", {})
            or "or:project" in self.parameters.parameters.get("group_by", {})
        ):
            annotations["project"] = F("namespace")

        return annotations 

def get_rank_window_function(self, group_by_value):
        """Generate a limit ranking window function."""
        tag_column = self._mapper.tag_column
        rank_orders = []
        rank_field = group_by_value.pop()
        default_ordering = self._mapper.report_type_map.get("default_ordering")

        if self.order_field == "delta" and "__" in self._delta:
            delta_field_one, delta_field_two = self._delta.split("__")
            rank_orders.append(getattr(F(delta_field_one) / F(delta_field_two), self.order_direction)())
        elif self.parameters.get("order_by", default_ordering):
            rank_orders.append(getattr(F(self.order_field), self.order_direction)())
        if tag_column in rank_field:
            rank_orders.append(self.get_tag_order_by(rank_field))
        else:
            rank_orders.append(getattr(F(rank_field), self.order_direction)())

        return Window(expression=RowNumber(), partition_by=F("date"), order_by=rank_orders) 

def test_execute_query_with_wildcard_tag_filter(self):
        """Test that data is filtered to include entries with tag key."""
        url = "?filter[time_scope_units]=month&filter[time_scope_value]=-1&filter[resolution]=monthly"  # noqa: E501
        query_params = self.mocked_query_params(url, AWSTagView)
        handler = AWSTagQueryHandler(query_params)
        tag_keys = handler.get_tag_keys()
        filter_key = tag_keys[0]
        tag_keys = ["tag:" + tag for tag in tag_keys]

        with tenant_context(self.tenant):
            totals = AWSCostEntryLineItemDailySummary.objects.filter(
                usage_start__gte=self.dh.this_month_start
            ).aggregate(**{"cost": Sum(F("unblended_cost") + F("markup_cost"))})

        url = f"?filter[time_scope_units]=month&filter[time_scope_value]=-1&filter[resolution]=monthly&filter[tag:{filter_key}]=*"  # noqa: E501
        query_params = self.mocked_query_params(url, AWSCostView)
        handler = AWSReportQueryHandler(query_params)
        data = handler.execute_query()
        data_totals = data.get("total", {})
        result = data_totals.get("cost", {}).get("total", {}).get("value")
        self.assertEqual(result, totals["cost"]) 

def test_populate_markup_cost_no_billsids(self):
        """Test that the daily summary table is populated."""
        summary_table_name = AZURE_REPORT_TABLE_MAP["line_item_daily_summary"]
        summary_table = getattr(self.accessor.report_schema, summary_table_name)

        query = self.accessor._get_db_obj_query(summary_table_name)
        with schema_context(self.schema):
            expected_markup = query.aggregate(markup=Sum(F("pretax_cost") * decimal.Decimal(0.1)))
            expected_markup = expected_markup.get("markup")
            summary_entry = summary_table.objects.all().aggregate(Min("usage_start"), Max("usage_start"))
            start_date = summary_entry["usage_start__min"]
            end_date = summary_entry["usage_start__max"]

        self.accessor.populate_markup_cost(0.1, start_date, end_date, None)
        with schema_context(self.schema):
            query = self.accessor._get_db_obj_query(summary_table_name).aggregate(Sum("markup_cost"))
            actual_markup = query.get("markup_cost__sum")
            self.assertAlmostEqual(actual_markup, expected_markup, 6) 

def test_populate_markup_cost_no_billsids(self):
        """Test that the daily summary table is populated."""
        summary_table_name = AWS_CUR_TABLE_MAP["line_item_daily_summary"]
        summary_table = getattr(self.accessor.report_schema, summary_table_name)

        query = self.accessor._get_db_obj_query(summary_table_name)
        with schema_context(self.schema):
            expected_markup = query.aggregate(markup=Sum(F("unblended_cost") * decimal.Decimal(0.1)))
            expected_markup = expected_markup.get("markup")

            summary_entry = summary_table.objects.all().aggregate(Min("usage_start"), Max("usage_start"))
            start_date = summary_entry["usage_start__min"]
            end_date = summary_entry["usage_start__max"]

        self.accessor.populate_markup_cost(0.1, start_date, end_date, None)
        with schema_context(self.schema):
            query = self.accessor._get_db_obj_query(summary_table_name).aggregate(Sum("markup_cost"))
            actual_markup = query.get("markup_cost__sum")
            self.assertAlmostEqual(actual_markup, expected_markup, 6) 

def filter_queryset(self, queryset):
        if self.request.user.is_anonymous():
            self.permission_denied(self.request)
        is_project = self.kwargs.get('is_project', None)
        pk = self.kwargs.get('pk', None)
        if is_project == "1":
            queryset = queryset.filter(project__id=pk)
            return queryset.annotate(response_count=Count("schedule_forms__project_form_instances")).select_related('schedule_forms', 'schedule_forms__xf', 'schedule_forms__em')
        else:
            project_id = get_object_or_404(Site, pk=pk).project.id
            queryset = queryset.filter(Q(site__id=pk, schedule_forms__from_project=False)
                                       | Q(project__id=project_id))
            return queryset.annotate(
                site_response_count=Count("schedule_forms__site_form_instances", ),
                response_count=Count(Case(
                    When(project__isnull=False, schedule_forms__project_form_instances__site__id=pk, then=F('schedule_forms__project_form_instances')),
                    output_field=IntegerField(),
                ), distinct=True)

            ).select_related('schedule_forms','schedule_forms__xf', 'schedule_forms__em') 

def increase_usage(self, bytes):
        # increase usage for this user based on document size
        storage_qs = UserStorage.objects.filter(pk=self.request.user.storage.pk)
        storage_qs.update(bytes_used=F("bytes_used") + bytes) 

def delete(self, *args, **kwargs):
        bytes_to_free = self.size
        super().delete(*args, **kwargs)
        storage_qs = UserStorage.objects.filter(pk=self.author.storage.pk)
        storage_qs.update(bytes_used=F("bytes_used") - bytes_to_free) 

def get_expected_results(self, **filter):
        """returns a list of dicts of the filtered user data
        """
        return list(
            get_user_model().objects.filter(**filter).annotate(
                fullname=F('profile__name'), country=F('profile__country')
                ).values(*self.expected_result_keys)) 

def get_expected_results(self, **filter):
        '''returns a list of dicts of the filtered user data
        
        '''
        return list(
            get_user_model().objects.filter(**filter).annotate(
                fullname=F('profile__name')).values(*self.expected_result_keys))

    # This test fails on 'assert 1'. More users are added after setup called 

def get_available_approvals(self, as_user):
        those_with_max_priority = With(
            TransitionApproval.objects.filter(
                workflow=self.workflow, status=PENDING
            ).values(
                'workflow', 'object_id', 'transition'
            ).annotate(min_priority=Min('priority'))
        )

        workflow_objects = With(
            self.wokflow_object_class.objects.all(),
            name="workflow_object"
        )

        approvals_with_max_priority = those_with_max_priority.join(
            self._authorized_approvals(as_user),
            workflow_id=those_with_max_priority.col.workflow_id,
            object_id=those_with_max_priority.col.object_id,
            transition_id=those_with_max_priority.col.transition_id,
        ).with_cte(
            those_with_max_priority
        ).annotate(
            object_id_as_str=Cast('object_id', CharField(max_length=200)),
            min_priority=those_with_max_priority.col.min_priority
        ).filter(min_priority=F("priority"))

        return workflow_objects.join(
            approvals_with_max_priority, object_id_as_str=Cast(workflow_objects.col.pk, CharField(max_length=200))
        ).with_cte(
            workflow_objects
        ).filter(transition__source_state=getattr(workflow_objects.col, self.field_name + "_id")) 

def delete(self, *args, **kwargs):
        fields_after = self.form.fields.filter(order__gte=self.order)
        fields_after.update(order=models.F("order") - 1)
        DocumentSetFieldEntry.objects.filter(field_id=self.id).delete()
        super(DocumentSetFormField, self).delete(*args, **kwargs) 

def persist(self, request_ip: str, archive: bool = True) -> None:
        metadata = self.get_metadata()

        # Increase counter of song/playlist
        with transaction.atomic():
            queryset = ArchivedSong.objects.filter(url=metadata["external_url"])
            if queryset.count() == 0:
                initial_counter = 1 if archive else 0
                archived_song = ArchivedSong.objects.create(
                    url=metadata["external_url"],
                    artist=metadata["artist"],
                    title=metadata["title"],
                    counter=initial_counter,
                )
            else:
                if archive:
                    queryset.update(counter=F("counter") + 1)
                archived_song = queryset.get()

            if archive:
                ArchivedQuery.objects.get_or_create(
                    song=archived_song, query=self.query
                )

        if self.musiq.base.settings.basic.logging_enabled and request_ip:
            RequestLog.objects.create(song=archived_song, address=request_ip) 

def persist(self, request_ip: str, archive: bool = True) -> None:
        if self.is_radio():
            return

        assert self.id
        if self.title is None:
            logging.warning("Persisting a playlist with no title (id %s)", self.id)
            self.title = ""

        with transaction.atomic():
            queryset = ArchivedPlaylist.objects.filter(list_id=self.id)
            if queryset.count() == 0:
                initial_counter = 1 if archive else 0
                archived_playlist = ArchivedPlaylist.objects.create(
                    list_id=self.id, title=self.title, counter=initial_counter
                )
                for index, url in enumerate(self.urls):
                    PlaylistEntry.objects.create(
                        playlist=archived_playlist, index=index, url=url,
                    )
            else:
                if archive:
                    queryset.update(counter=F("counter") + 1)
                archived_playlist = queryset.get()

        if archive:
            ArchivedPlaylistQuery.objects.get_or_create(
                playlist=archived_playlist, query=self.query
            )

        if self.musiq.base.settings.basic.logging_enabled and request_ip:
            RequestLog.objects.create(playlist=archived_playlist, address=request_ip) 

def vote_up(self, request: WSGIRequest) -> HttpResponse:
        """Increases the vote-count of the given song by one."""
        key = request.POST.get("key")
        if key is None:
            return HttpResponseBadRequest()
        ikey = int(key)

        models.CurrentSong.objects.filter(queue_key=ikey).update(votes=F("votes") + 1)
        self.playback.queue.vote_up(ikey)
        return HttpResponse() 

def vote_down(self, request: WSGIRequest) -> HttpResponse:
        """Decreases the vote-count of the given song by one.
        If a song receives too many downvotes, it is removed."""
        key = request.POST.get("key")
        if key is None:
            return HttpResponseBadRequest()
        ikey = int(key)

        models.CurrentSong.objects.filter(queue_key=ikey).update(votes=F("votes") - 1)
        try:
            current_song = models.CurrentSong.objects.get()
            if (
                current_song.queue_key == ikey
                and current_song.votes
                <= -self.musiq.base.settings.basic.downvotes_to_kick
            ):
                with self.playback.mopidy_command() as allowed:
                    if allowed:
                        self.playback.player.playback.next()
        except models.CurrentSong.DoesNotExist:
            pass

        removed = self.playback.queue.vote_down(
            ikey, -self.musiq.base.settings.basic.downvotes_to_kick
        )
        # if we removed a song by voting, and it was added by autoplay,
        # we want it to be the new basis for autoplay
        if removed is not None:
            self.playback.queue_semaphore.acquire(blocking=False)
            if not removed.manually_requested:
                self.playback.handle_autoplay(removed.external_url or removed.title)
            else:
                self.playback.handle_autoplay()
        return HttpResponse() 

def dequeue(self) -> Tuple[int, Optional["QueuedSong"]]:
        """Removes the first completed song from the queue and returns its id and the object."""
        song = self.confirmed().first()
        if song is None:
            return -1, None
        song_id = song.id
        song.delete()
        self.filter(index__gt=song.index).update(index=F("index") - 1)
        return song_id, song 

def remove(self, key: int) -> "QueuedSong":
        """Removes the song specified by :param key: from the queue and returns it."""
        to_remove = self.get(id=key)
        to_remove.delete()
        self.filter(index__gt=to_remove.index).update(index=F("index") - 1)
        return to_remove 

def vote_up(self, key: int) -> None:
        """Increase the vote-count of the song specified by :param key:."""
        self.filter(id=key).update(votes=F("votes") + 1) 

def vote_down(self, key: int, threshold: int) -> Optional["QueuedSong"]:
        """Decrease the vote-count of the song specified by :param key:
        If the song is now below the threshold, remove and return it."""
        self.filter(id=key).update(votes=F("votes") - 1)
        try:
            song = self.get(id=key)
            if song.votes <= threshold:
                song.delete()
                return song
        except core.models.QueuedSong.DoesNotExist:
            pass
        return None 

def annotate_expires_at(self):
        """Add an ``expires_at`` field to the queryset results."""
        return self.annotate(
            expires_at=ExpressionWrapper(
                (F('last_beat') + F('timeout')),
                output_field=EXPIRES_COLUMN_TYPE
            )
        ) 

def status_by_name(self):
        """Return the expired status for every heartbeat."""
        # Sadly, tests like (F('last_beat') + F('timeout')) < now() aren't supported in Django.
        # Even this fails: .annotate(is_expired=RawSQL("(last_beat + timeout) < %s", [now()]))
        # Thus, have to make the comparison in Python instead.
        t = now()
        monitors = self.annotate_expires_at().values_list('name', 'expires_at')
        return {
            name: (expires_at < t) for name, expires_at in monitors
        } 

def username_to_email(apps, schema_editor):
    """Sets the email for users that don't have an email."""
    User = apps.get_model("auth", "User")
    users = User.objects.filter(email="", username__contains="@")
    users.update(email=F("username")) 

def bidindex(request, event=None):
    event = viewutil.get_event(event)

    if not event.id:
        return HttpResponseRedirect(
            reverse('tracker:bidindex', args=(Event.objects.latest().short,))
        )

    bids = Bid.objects.filter(state__in=('OPENED', 'CLOSED'), event=event).annotate(
        speedrun_name=F('speedrun__name'), event_name=F('event__name')
    )

    toplevel = [b for b in bids if b.parent_id is None]
    total = sum((b.total for b in toplevel), 0)
    choiceTotal = sum((b.total for b in toplevel if not b.goal), 0)
    challengeTotal = sum((b.total for b in toplevel if b.goal), 0)

    bids = [get_bid_info(bid, bids) for bid in bids if bid.parent_id is None]

    if event.id:
        bidNameSpan = 2
    else:
        bidNameSpan = 1

    return views_common.tracker_response(
        request,
        'tracker/bidindex.html',
        {
            'bids': bids,
            'total': total,
            'event': event,
            'bidNameSpan': bidNameSpan,
            'choiceTotal': choiceTotal,
            'challengeTotal': challengeTotal,
        },
    ) 

def prizes_with_winner_accept_email_pending(event):
    return PrizeWinner.objects.filter(
        Q(prize__event=event)
        & Q(prize__state='ACCEPTED')
        & Q(acceptcount__gt=F('acceptemailsentcount'))
    ) 

def filter_text(self, queryset, name, value):
        """Full-text search."""
        query = SearchQuery(value, config="simple")
        return (
            queryset.filter(**{name: query})
            # This assumes that field is already a TextSearch vector and thus
            # doesn't need to be transformed. To achieve that F function is
            # required.
            .annotate(rank=SearchRank(F(name), query)).order_by("-rank")
        ) 

def sales_by_product(from_date, to_date):
    sales = OrderItem.objects.filter(
        Q(order__payment_date__lte=to_date) & Q(order__payment_date__gte=from_date)
    ).exclude(
        order__status=Order.CANCELLED
    ).annotate(
        title=F('product__product__title')
    ).values(
        'title'
    ).annotate(
        quantity=Sum('quantity')
    ).order_by('-quantity')

    return sales