Few questions in admin & ORM?

Using Django The Admin
1

Hello,

I have multiple issues that I couldn’t solve by my own.

  1. in CaseManager.get_queryset() : I’m trying to add a where clause that the case__id is equal to movement__case__id as:
queryset=Movement.objects.filter(case__id=models.F('case__id')).order_by('-date'),

but the query becomes as

...
WHERE ("cm_movement"."case_id" = ("cm_movement"."case_id") AND 
...

Full query:

SELECT "cm_movement"."id",
       "cm_movement"."case_id",
       "cm_movement"."direction",
       "cm_movement"."date",
       "cm_movement"."reason_id",
       "cm_movement"."details",
       "cm_movement"."created_at",
       "cm_movement"."updated_at",
       "cm_case"."id",
       "cm_case"."police_station_id",
       "cm_case"."number",
       "cm_case"."year",
       "cm_case"."kind",
       "cm_case"."registered_at",
       "cm_case"."created_at",
       "cm_case"."updated_at",
       "cm_movement_reason"."id",
       "cm_movement_reason"."direction",
       "cm_movement_reason"."reason",
       "cm_movement_reason"."created_at",
       "cm_movement_reason"."updated_at"
  FROM "cm_movement"
 INNER JOIN "cm_case"
    ON ("cm_movement"."case_id" = "cm_case"."id")
 INNER JOIN "cm_movement_reason"
    ON ("cm_movement"."reason_id" = "cm_movement_reason"."id")
 WHERE ("cm_movement"."case_id" = ("cm_movement"."case_id") AND "cm_movement"."case_id" IN ('41', '35', '29', '26', '44', '40', '34', '39', '32', '38', '31', '33', '30', '28', '27', '25', '43', '37', '36', '42'))
 ORDER BY "cm_movement"."date" DESC

  1. how can I get only the earliest or latest (only one movement) which is in prefetch_related in CaseManager.get_queryset()

  2. how can I sort the result by latest_movement that is being generated by CaseManager.get_queryset() and used in: CaseAdmin.latest_movement()

  3. in CaseAdmin.ordering should sort the results by year in DESC order, and if there is more than one record with the same year they should be sorted by their number in DESC order that’s why I used CaseAdmin.ordering = ['-year', '-number'] but what happened is they got sorted by year only. ex:
    Screen Shot 2023-06-03 at 4.56.18 PM
    how to fix it?

here are my codes:

Managers:

class PoliceStationManager(models.Manager):
    def get_queryset(self, *args, **kwargs) -> models.QuerySet:
        return super().get_queryset(*args, **kwargs).annotate(
            misdemeanors_count=models.Count('case', filter=models.Q(case__kind=Case.Kind.MISDEMEANOR)),
            traffics_count=models.Count('case', filter=models.Q(case__kind=Case.Kind.TRAFFIC)),
            cases_count=models.Count('case'),
        )


class CaseManager(models.Manager):
    def get_queryset(self, *args, **kwargs) -> models.QuerySet:
        return super(CaseManager, self).get_queryset(*args, **kwargs).select_related('police_station').prefetch_related(
            models.Prefetch(
                'movement_set',
                queryset=Movement.objects.filter(case__id=models.F('case__id')).order_by('-date'),
                # Does not work:
                # queryset=Movement.objects.filter(case_id=models.F('case__id')).earliest('-date'),
                to_attr='latest_movement'
            )
        )


class MovementManager(models.Manager):
    def get_queryset(self, *args, **kwargs) -> models.QuerySet:
        return super(MovementManager, self).get_queryset(*args, **kwargs).select_related(
            'reason', 'case').prefetch_related('case__police_station')

Models:

TABLE_PREFIX = 'cm_'  # cases_
TABLE_SUFFIX = ''     # _tbl

# Create your models here.


class PoliceStation(models.Model):
    class Meta:
        db_table = f'{TABLE_PREFIX}policestation{TABLE_SUFFIX}'
        verbose_name = _('police station')
        verbose_name_plural = _('police stations')

    objects = PoliceStationManager()

    id = models.BigAutoField(
        unique=True, primary_key=True, null=False, blank=False, db_index=True,
        db_column='id', verbose_name=_('Case ID'))
    name = models.CharField(
        unique=False, null=False, blank=False, db_index=True,
        db_column='name', verbose_name=_('Police Station Name'),
        max_length=25)
    created_at = models.DateTimeField(
        auto_now=False, auto_now_add=True, db_column='created_at',
        verbose_name=_('Created at'))
    updated_at = models.DateTimeField(
        auto_now=True, auto_now_add=False, db_column='updated_at',
        verbose_name=_('Updated at'))

    def __str__(self):
        return f'{self.name}'


class Case(models.Model):
    class Meta:
        db_table = f'{TABLE_PREFIX}case{TABLE_SUFFIX}'
        verbose_name = _('case')
        verbose_name_plural = _('cases')
        ordering = ['-year', '-number', 'police_station__name', 'kind']
        constraints = [
            models.UniqueConstraint(fields=['number', 'year', 'kind', 'police_station'], name='unique_case2')]

    class Kind(models.TextChoices):
        MISDEMEANOR = 'M', _('Misdemeanor')
        TRAFFIC = 'T', _('Traffic')

    objects = CaseManager()

    id = models.BigAutoField(
        unique=True, primary_key=True, null=False, blank=False, db_index=True,
        db_column='id', verbose_name=_('Case ID'))
    police_station = models.ForeignKey(
        PoliceStation, on_delete=models.PROTECT,
        unique=False, null=False, blank=False, db_index=True,
        db_column='police_station_id', verbose_name=_('Police Station'))
    number = models.CharField(
        unique=False, null=False, blank=False, db_index=True,
        db_column='number', verbose_name=_('Case Number'),
        max_length=4)
    year = models.CharField(
        unique=False, null=False, blank=False, db_index=True,
        db_column='year', verbose_name=_('Case Year'),
        max_length=4)
    kind = models.CharField(
        unique=False, null=False, blank=False, db_index=False,
        db_column='kind', verbose_name=_('Case Kind'),
        max_length=1, choices=Kind.choices, default=Kind.MISDEMEANOR)
    registered_at = models.DateTimeField(
        auto_now=False, auto_now_add=False, db_column='registered_at', db_index=True,
        null=True, default=timezone.now,    # TODO: remove this line, once we update all current db records
        verbose_name=_('Registration Date'))
    created_at = models.DateTimeField(
        auto_now=False, auto_now_add=True, db_column='created_at',
        verbose_name=_('Created at'))
    updated_at = models.DateTimeField(
        auto_now=True, auto_now_add=False, db_column='updated_at',
        verbose_name=_('Updated at'))

    def __str__(self):
        context = {
            'number': self.number,
            'year': self.year,
            'kind': self.get_kind_display(),
            # 'police_station': '???'
            'police_station': self.police_station.name
        }
        return _('%(number)s/%(year)s %(kind)s %(police_station)s') % context


class MovementReason(models.Model):
    class Meta:
        db_table = f'{TABLE_PREFIX}movement_reason{TABLE_SUFFIX}'
        verbose_name = _('movement reason')
        verbose_name_plural = _('movement reasons')
        ordering = ['direction']

    class Direction(models.TextChoices):
        SENT = 'S', _('Outbound')
        RETURNED = 'R', _('Inbound')

    id = models.BigAutoField(
        unique=True, primary_key=True, null=False, blank=False, db_index=True,
        db_column='id', verbose_name=_('Movement Reason ID'))
    direction = models.CharField(
        unique=False, null=False, blank=False, db_index=False,
        db_column='direction', verbose_name=_('Movement Type'),
        max_length=1, choices=Direction.choices, default=Direction.SENT)
    reason = models.CharField(
        unique=False, null=False, blank=False, db_index=True,
        db_column='reason', verbose_name=_('Reason'),
        max_length=50)
    created_at = models.DateTimeField(
        auto_now=False, auto_now_add=True, db_column='created_at',
        verbose_name=_('Created at'))
    updated_at = models.DateTimeField(
        auto_now=True, auto_now_add=False, db_column='updated_at',
        verbose_name=_('Updated at'))

    def __str__(self):
        return f'{self.get_direction_display()}: {self.reason}'


class Movement(models.Model):
    class Meta:
        db_table = f'{TABLE_PREFIX}movement{TABLE_SUFFIX}'
        verbose_name = _('movement')
        verbose_name_plural = _('movements')

    class Direction(models.TextChoices):
        SENT = 'S', _('Sent')
        RETURNED = 'R', _('Returned')

    objects = MovementManager()

    id = models.BigAutoField(
        unique=True, primary_key=True, null=False, blank=False, db_index=True,
        db_column='id', verbose_name=_('Movement ID'))
    case = models.ForeignKey(
        Case, on_delete=models.CASCADE,
        unique=False, null=False, blank=False, db_index=True,
        db_column='case_id',
        verbose_name=_('Case number'))
    direction = models.CharField(
        unique=False, null=False, blank=False, db_index=False,
        db_column='direction', verbose_name=_('Movement Direction'),
        max_length=1, choices=Direction.choices, default=Direction.SENT)
    date = models.DateField(
        auto_now=False, auto_now_add=False, db_column='date', db_index=True,
        verbose_name=_('Movement Date'))
    reason = models.ForeignKey(
        MovementReason, on_delete=models.PROTECT,
        unique=False, null=False, blank=False, db_index=True,
        db_column='reason_id',
        verbose_name=_('Movement Reason'))
    details = models.TextField(
        unique=False, null=False, blank=True, db_index=False,
        db_column='details',
        verbose_name=_('Movement Details'))
    created_at = models.DateTimeField(
        auto_now=False, auto_now_add=True, db_column='created_at',
        verbose_name=_('Created at'))
    updated_at = models.DateTimeField(
        auto_now=True, auto_now_add=False, db_column='updated_at',
        verbose_name=_('Updated at'))

    def __str__(self):
        on_date = _('on date')
        return f"{self.case} {self.get_direction_display()} {on_date} {self.date}: {self.reason}"

app admin:

# ------------------------------
# Cases
# ------------------------------


# admin.site.register(Case)
@admin.register(Case)
class CaseAdmin(myadmin.DefaultModelAdmin):
    list_display = ['case_number', 'number', 'year', 'kind', 'police_station_name', 'registered_at', 'latest_movement']
    list_select_related = ['police_station']
    list_select_prefetch = ['movement_sets']
    fields = ['registered_at', ('number', 'year'), ('kind', 'police_station'), ('created_at', 'updated_at',)]
    ordering = ['-year', '-number']
    date_hierarchy = 'registered_at'

    # Computed columns

    @admin.display(empty_value="???", ordering='police_station__name', description=_('Police Station Name'))
    def police_station_name(self, case):
        anchor_url = (
            reverse('admin:case_movements_policestation_changelist')
            + '?'
            + urlencode({
                # 'id': case.police_station_id,
                'id': case.police_station.id,
            })
        )
        anchor_title = case.police_station.name
        return format_html(f'<a href="{anchor_url}">{anchor_title}</a>') if anchor_title else ''

    @admin.display(empty_value="", ordering='movement__date', description=_('Latest movement'))
    # @admin.display(empty_value="", ordering='movement__latest_movement', description=_('Latest movement'))
    def latest_movement(self, case):
        if hasattr(case, 'latest_movement') and case.latest_movement:
            movement = case.latest_movement[0]
            anchor_url = (
                reverse('admin:case_movements_movement_changelist')
                + '?'
                + urlencode({
                    'case__id': case.id,
                })
            )
            anchor_title = _('%(direction)s: %(reason)s on %(date)s') % {
                'direction': movement.reason.get_direction_display(),
                'reason': movement.reason.reason,
                'date': movement.date,
            }
            return format_html(f'<a href="{anchor_url}">{anchor_title}</a>') if anchor_title else ''
        return None

    @admin.display(ordering=Concat('year', Value(' '), 'number'))
    def case_number(self, case):
        return f'{case.number} / {case.year}'


# ------------------------------
# Police Stations
# ------------------------------


@admin.register(PoliceStation)
class PoliceStationAdmin(myadmin.DefaultModelAdmin):
    list_display = ['fullname', 'misdemeanors_count', 'traffics_count', 'cases_count']
    fields = ['name', ('created_at', 'updated_at',)]
    ordering = ['name']

    # Computed columns

    @admin.display(description=_('Police Station Name'), ordering='name')
    def fullname(self, police_station):
        return f'{_("police station")} {police_station.name}'

    @admin.display(description=_('Misdemeanors count'), ordering='misdemeanors_count')
    def misdemeanors_count(self, police_station):
        anchor_url = (
            reverse('admin:case_movements_case_changelist')
            + '?'
            + urlencode({
                'police_station_id': police_station.id,
                'kind': Case.Kind.MISDEMEANOR,
            })
        )
        anchor_title = police_station.misdemeanors_count
        return format_html(f'<a href="{anchor_url}">{anchor_title}</a>') if anchor_title > 0 else anchor_title

    @admin.display(description=_('Traffics count'), ordering='traffics_count')
    def traffics_count(self, police_station):
        anchor_url = (
            reverse('admin:case_movements_case_changelist')
            + '?'
            + urlencode({
                'police_station_id': police_station.id,
                'kind': Case.Kind.TRAFFIC,
            })
        )
        anchor_title = police_station.traffics_count
        return format_html(f'<a href="{anchor_url}">{anchor_title}</a>') if anchor_title > 0 else anchor_title

    @admin.display(description=_('Total cases'), ordering='cases_count')
    def cases_count(self, police_station):
        anchor_url = (
            reverse('admin:case_movements_case_changelist')
            + '?'
            + urlencode({
                'police_station_id': police_station.id,
            })
        )
        anchor_title = police_station.cases_count
        return format_html(f'<a href="{anchor_url}">{anchor_title}</a>') if anchor_title > 0 else anchor_title


# ------------------------------
# Movements
# ------------------------------


@admin.register(Movement)
class MovementAdmin(myadmin.DefaultModelAdmin):
    list_display = ['case', 'date', 'direction', 'computed_reason']
    list_select_related = ['case', 'case__police_station', 'reason']
    fields = [('case', 'date'), ('direction', 'reason'), 'details', ('created_at', 'updated_at')]
    ordering = ['-date']
    date_hierarchy = 'date'

    # Computed columns

    @admin.display(description=_('movement reason'), empty_value="???", ordering='reason__reason')
    def computed_reason(self, movement):
        anchor_url = (
            reverse('admin:case_movements_movementreason_changelist')
            + '?'
            + urlencode({
                'id': movement.reason.id,
            })
        )
        anchor_title = movement.reason.reason
        return format_html(f'<a href="{anchor_url}">{anchor_title}</a>') if anchor_title else ''


# ------------------------------
# Movement Reasons
# ------------------------------


@admin.register(MovementReason)
class MovementReasonAdmin(myadmin.DefaultModelAdmin):
    list_display = ['direction', 'reason']
    fields = [('direction', 'reason',), ('created_at', 'updated_at')]
    readonly_fields_in_edit = ('created_at', 'updated_at')
    exclude_in_add = ('created_at', 'updated_at')
    ordering = ['direction']

And here is my custom DefaultModelAdmin:

class DefaultModelAdmin(admin.ModelAdmin):
    # Displaying fields
    list_display = ('__str__',)

    # Select related table in the sql query
    list_select_related = False

    # Editable fields records
    list_editable = ()

    ''' Exclude fields from the form in add & edit pages.
        This will override readonly_fields '''
    exclude = ()

    ''' Exclude fields from the form in add page
        This will override readonly_fields '''
    exclude_in_add = ()  # ['created_at', 'updated_at',]

    ''' Exclude fields from the form in edit page
        This will override readonly_fields '''
    exclude_in_edit = ()

    # Grouping fields. ex: fields = [('number', 'year'), ('kind', 'police_station')]
    # if it's not declared, then it will include all fields by default.
    # if it's declared to an empty value (regardless of the type) then it won't include any field
    # fields = []

    # fields to be read-only mode in add & edit pages
    readonly_fields = ()

    # fields to be read-only mode in add page
    readonly_fields_in_add = ()

    # fields to be read-only mode in edit page
    readonly_fields_in_edit = ()  # ['created_at', 'updated_at',]

    # Control complex layout. structure
    """
        * classes:
           - A list or tuple containing extra CSS classes to
             apply to the fieldset.
        * fields:
            - A list or tuple of field names to display in this
              fieldset. This key is required.
            - As with the fields option, to display multiple fields
              on the same line, wrap those fields in their own tuple.
            - fields can contain values defined in readonly_fields
              to be displayed as read-only.
            - If you add the name of a callable to fields, the same
              rule applies as with the fields option: the callable
              must be listed in readonly_fields.
        * description:
            - A text to be displayed at the top of each fieldset,
              under the heading of the fieldset. This string is
              not rendered for TabularInline due to its layout.
            - Note that this value is not HTML-escaped when it’s
              displayed in the admin interface. This lets you
              include HTML if you so desire. Alternatively you
              can use plain text and django.utils.html.escape()
              to escape any HTML special characters.
    """
    # fieldsets = [
    #    (
    #        None,
    #        {
    #            'fields': ['field1', 'field2', 'field3', ]
    #        },
    #    ),
    #    (
    #        'Title 1',
    #        {
    #            'description': 'Some text goes here..',
    #            'classes': ['collapse'],
    #            'fields': ['field1', 'field2', 'field3', ]
    #        },
    #    ),
    # ]

    # Filtering
    # see:
    #       https://docs.djangoproject.com/en/4.2/ref/contrib/admin/#django.contrib.admin.ModelAdmin.filter_horizontal
    #       https://stackoverflow.com/questions/22968631/how-to-filter-filter-horizontal-in-django-admin
    filter_horizontal = ()
    filter_vertical = ()

    # Specify the model-form to use. ex: form = CaseForm
    # form =

    # Sorting records
    ordering = []

    # Dates
    date_hierarchy = None

    # Records per page
    list_per_page = 20

    # Page Actions
    actions_on_top = True
    actions_on_bottom = True
    actions_selection_counter = True

    # True, “Save and add another” will be replaced by a
    # “Save as new” button that creates a new object 
    # (with a new ID) rather than updating the existing object.
    save_as = False

    # Replace empty values
    empty_value_display = ''

    # Provides a quick-and-dirty way to override some Field options for use in the admin.
    # Ex: use RichTextEditorWidget for large text fields instead of the default <textarea>
    formfield_overrides = {}

    # https://docs.djangoproject.com/en/4.2/ref/contrib/admin/#django.contrib.admin.ModelAdmin.inlines
    inlines = ()

    # ------------------------------
    # Override admin`s methods
    # ------------------------------

    def get_readonly_fields(self, request, obj=None):
        """ What fields to be in readonly mode in add and/or edit pages
            * readonly_fields           available in    add and edit pages
            * readonly_fields_in_add    available in    add page
            * readonly_fields_in_edit   available in    edit page """
        readonly = list(self.readonly_fields) if self.readonly_fields else []
        if obj:  # editing an existing object
            return readonly + iterate(self.readonly_fields_in_edit, True) if self.readonly_fields_in_edit else readonly
        return readonly + iterate(self.readonly_fields_in_add, True) if self.readonly_fields_in_add else readonly

    def get_fields(self, request, obj=None):
        """ what fields to show, exclude and make readonly in add & edit pages"""
        return scan_and_remove(self.fields, self.get_exclude(request, obj))

    def get_exclude(self, request, obj=None):
        """ What fields to be excluded in add and/or edit pages
            * exclude           from    add and edit pages
            * exclude_in_add    from    add page
            * exclude_in_edit   from    edit page """
        exclude = list(self.exclude) if self.exclude else []
        if obj:  # editing an existing object
            return exclude + iterate(self.exclude_in_edit, True) if self.exclude_in_edit else exclude
        return exclude + iterate(self.exclude_in_add, True) if self.exclude_in_add else exclude

Sorry for the long post. I just wanted to provide you with everything, so it’s clear to you where my mistakes are.

Thanks in advance for any help.

2

I will point out that the ordering is working here exactly as expected. They are sorted by case number within year. However, you’re storing the case number as a character field not as a numeric field, so it’s ordering them as strings and not by numeric values. As a results, ‘-number’ is going to sort ‘5’ before ‘4’, ‘40’, ‘400’, etc.

3

you’re right, I noticed that earlier. but thanks anyway