In Django CMS, how can I prevent duplicate items in a queryset when I order them by .order_by('title_set__title')

Question

Let's say I am viewing a changelist for a set of pages in a Django CMS project and I want to sort that by title. But for each title/language that is created and published, a duplicate tree item will be displayed. For any other kind of sorting that I have created, this does not happen and it works as it should. This will return one tree item for each language that has been published and I am pretty sure created as well. Even with the .distinct() it will return one tree item for each title/language that exists. Examples below.

This example produces duplicate or even 3, 4, 5 copies of the same tree item if there are that many language titles that exist, such as ('en', 'fr', 'de, and 'es' titles).

def get_queryset(self, request):
    queryset = super(PageAdmin, self).get_queryset(request)
    queryset = queryset.order_by('title_set__title').distinct()

Now in this example, if I order tree items by anything else, or (-publication_date, publication_end_date, -publication_end_date). I will only get one result for pages that have multiple titles/languages that exist, just like it should.

def get_queryset(self, request):
    queryset = super(PageAdmin, self).get_queryset(request)
    queryset = queryset.order_by('publication_date').distinct()

My educated guess is that it has something to do with the "title_set", which is being prefetched elsewhere, being a collection of many titles (1 for each language) versus it being a single title, the language that is selected in the change-list results.

In both of these methods;

def changelist_view(self, request, extra_context=None):

and

def get_tree(self, request):

of

Class BasePageAdmin(PlaceholderAdminMixin, admin.ModelAdmin):

This is where the prefetch is running. These all came standard with the package. I have modified some things as I am building a custom system around this, but this part is still mostly how the package came. So I'm guessing I just need a better understanding about how either Django-CMS works and/or how prefetch_related() can be used. My knowledge is not Master Jedi level yet.

pages = pages.prefetch_related(
    Prefetch(
        'title_set',
        to_attr  = 'filtered_translations',
        queryset = Title.objects.filter(language__in=get_language_list(site.pk))
    ),
)

I'm running these major components;

Django==2.2.9

django-cms==3.7.1

python 3.7.8

Windows 10

Answer 1

If anyone runs into this and needs a solution, I found a solution or at least a good work around. If this is not the proper way to sort a list of Pages by its related Title.title in Django-CMS then I would love to learn the right way.

What I ended up doing was sorting/ordering my queryset the way that I wanted, by the title of the related Title of the Page. I then converted the queryset into a values_list of id's of Pages. I then removed any duplicates within that list and then taxed another query to the database to get a new queryset, retrieving only the id's in the values_list and preserves the order I had the values_list in. Since I am performing another query of the database to get a new set of Pages, this is where I don't think this is the proper way to do this, but it will definitely get the job done and you won't have duplicates. If someone has a better way please share.

from django.db.models import Case, When

def get_queryset(self, request):
    queryset = super(PageAdmin, self).get_queryset(request)

    pk_list = queryset.order_by('title_set__title').values_list('id', flat=True)

    pk_list_cleaned = []
    [pk_list_cleaned.append(x) for x in pk_list if x not in pk_list_cleaned]

    preserved_order = Case(*[When(pk=pk, then=pos) for pos, pk in enumerate(pk_list_cleaned)])
    queryset = Page.objects.filter(pk__in=pk_list_cleaned).order_by(preserved_order)