Admin list filter, grouped by first letter and expanded group

I have a model Source with a foreign key to model Place. On the changelist page of the Source model, I would like a list filter that shows all letters of the alphabet, and when clicked on 'A' shows all place names starting with 'A', which are clickable to show only Sources in the list that have that place in there fk field.

Simplified models:

class Source(models.Model):
    place = models.ForeignKey(Place, null=True, place=True, on_delete=models.SET_NULL

class Place(models.Model):
    name = models.CharField(max_length = 255, primary_key=True)

I currently have this:

class PlaceFilter(admin.SimpleListFilter):
    title = 'first letter of place name'
    parameter_name = 'letter'

    def lookups(self, request, model_admin):
        qs = model_admin.get_queryset(request)
        options = qs.values_list(Substr('place__name', 1, 1), Substr('place__name', 1, 1)) \
                    .annotate(fl=Count(Substr('place__name', 1, 1))).distinct() \
                    .order_by(Substr('place__name', 1, 1))
        options = [(i, f'{j} ({k})') for i,j,k in options]

        return options

    def queryset(self, request, queryset):
        if self.value():
            return queryset.filter(place__name__istartswith=self.value())

Which does show all the letters, but when clicked on a letter the list contains all sources with a place that starts with that letter. Furthermore, I'm in the Netherlands and some places here have names that start with 's, 't, sometimes followed by a hyphen, sometimes not. These names should not be grouped by the starting single quote, but (usually) by the 4th letter, i.e. 's-Gravenhage should be under 'G'.

I don't know if I need the count here, I would prefer not to have it always in the list, but if I try and use the built-in count function of the list filters I keep getting errors about like this:

ValueError at /admin/importer/source/
Column aliases cannot contain whitespace characters, quotation marks, semicolons, or SQL comments.

I've tried several different options, even filtering the names out before going into de queryset() method.

UPDATE:
By now with the code below, I do get a list of the letters in the alphabet, and when clicking on one of those, the place names starting with that letter do appear, and are correctly clickable. But I still get the error about the Column aliases when I click on 'show counts'...

class PlaceFilter(admin.SimpleListFilter):
    title = 'first letter of place name'
    parameter_name = 'letter'

    def lookups(self, request, model_admin):
        qs = model_admin.get_queryset(request)
        letters = list(string.ascii_uppercase)
        options = [(letter, letter) for letter in letters]
        if val := self.value():
            print(val)
            if len(val) == 1:
                sub_options = list(qs.values_list('place__name', 'place__name').distinct() \
                            .filter(place__name__iregex=rf"^('s-|'s )?{val}") \
                            .order_by('place__name'))
                val_index = options.index((val, val))

                index = val_index + 1
                for option in sub_options:
                    options.insert(index, option)
                    index += 1
                        
        return list(options)
    
    def queryset(self, request, queryset):
        if self.value() and len(self.value()) > 1:
            return queryset.filter(place__name=self.value())

I solved it with this code. Now even when I click on a place name, the other place names with the same first letter will stay opened. And the counter works as well :)

class PlaceFilter(admin.SimpleListFilter):
    title = 'first letter of place name'
    parameter_name = 'letter'

    def get_sub_options(self, letter, qs):
        return list(qs.values_list('place__name', 'place__name').distinct() \
                .filter(place__name__iregex=rf"^('s-|'s |'t )?{letter}") \
                .order_by('place__name'))

    def lookups(self, request, model_admin):
        qs = model_admin.get_queryset(request)
        letters = list(string.ascii_uppercase)
        options = [(letter, letter) for letter in letters]
        if self.value():
            if len(self.value()) == 1:
                first_letter = self.value()
                sub_options = self.get_sub_options(first_letter, qs)
                val_index = options.index((self.value(), self.value()))

                index = val_index + 1
                for option in sub_options:
                    options.insert(index, option)
                    index += 1
            elif len(self.value()) > 1:
                first_letter = re.sub(fr"^('s-|'s |'t )", "",self.value())[0]
                sub_options = self.get_sub_options(first_letter, qs)
                val_index = options.index((first_letter, first_letter))

                index = val_index + 1
                for option in sub_options:
                    options.insert(index, option)
                    index += 1
                        
        return list(options)
    
    def queryset(self, request, queryset):
        if self.value():
            if len(self.value()) == 1:
                return queryset.filter(place__name__istartswith=self.value())
            if len(self.value()) > 1:
                return queryset.filter(place__name=self.value())
Вернуться на верх