I want to bring users who have not updated their salary information in the last 1 year. BUT WITH ORM not For Loop.

from simple_history.models import HistoricalRecords



class User(AbstractUser):
    ...
    salary_expectation = models.IntegerField()
    history = HistoricalRecords(cascade_delete_history=True)
    

################################################################

    User.objects.filter(# MAGIC )  # Get users who have NOT updated their salary information in the last year

CodePudding user response：

I can see that this is a package which has its documentation in querying its entries, see below: https://django-simple-history.readthedocs.io/en/latest/querying_history.html

nevertheless you can do that intuitively following Django's normal behavior and a couple of SQL knowledge, I'd expect that history field's table most likely has a one-to-many relationship with the users table, so what I'd do is first open the database, find the column that shows the date of change, write down its name and then write this ORM query below

sub_query =  ~Q(history__history_date__lte= "Replace with end of date", history__history_date__gte= "Replace with beginning of date", salary_expectation__isnull=False)

users = User.objects.filter(sub_query)

dont forget to import Q

from django.db.models import Q

CodePudding user response：

You do not need to check HistoricalRecords class for this information.

1. Add created_at and updated_at (date_time_fields) fields to your User model

class User(...):
    ...
    created_at = models.DateTimeField(auto_now_add=True)
    updated_at = models.DateTimeField(auto_now=True)

2. Queryset Code

from django.db.models.functions import Now, ExtractDay
from django.contrib.auth import get_user_model

User = get_user_model()

users = User.objects.annotate(
                # Calculate duration between now and last update date saved
                duration=models.ExpressionWrapper(
                    Now() - models.F("updated_at"),
                    output_field=models.DurationField()
                ),

                # Extract the amount of days in the duration
                days=ExtractDay('duration'),

                # Check if the number of days between the 2 fields exceeds 1 year (365.25 Days)
                last_update_beyond_a_year=models.Case(
                    models.When(
                        models.Q(days__gte=365.25),
                        then=True
                    ),
                    default=False,
                    output_field=models.BooleanField()
                )
            # Then filter 
            ).filter(last_update_beyond_a_year=True)

and Voila !