Django's distinct() with Field Arguments Issue

Problem Explanation

The error message DISTINCT ON fields is not supported by this database backend occurs because you're trying to use the PostgreSQL-specific feature of distinct() with field arguments on a MySQL database.

According to the Django documentation, passing field arguments to distinct() (like distinct('name_id') in your code) is only supported on PostgreSQL databases. This feature translates to a SELECT DISTINCT ON SQL query, which is a PostgreSQL-specific syntax.

Solution Options

Since you're using MySQL 8.0, you have a few options to achieve similar functionality:

Option 1: Use distinct() without field arguments

PYTHONCopied
portals = Portal.objects.filter(
    ~Q(customer_id=0),
    customer_id=customer_id,
).order_by('-updated_at').distinct()

This will remove all duplicate rows, but may not give you exactly what you want if you're trying to get the most recently updated record for each name_id.

Option 2: Use a subquery approach

PYTHONCopied
from django.db.models import Max, Subquery, OuterRef

# Get the latest updated_at for each name_id
latest_updates = Portal.objects.filter(
    ~Q(customer_id=0),
    customer_id=customer_id,
    name_id=OuterRef('name_id')
).order_by('-updated_at').values('updated_at')[:1]

# Use the subquery to filter the main queryset
portals = Portal.objects.filter(
    ~Q(customer_id=0),
    customer_id=customer_id,
    updated_at__in=Portal.objects.filter(
        ~Q(customer_id=0),
        customer_id=customer_id
    ).values('name_id').annotate(
        latest=Max('updated_at')
    ).values('latest')
)

Option 3: Use raw SQL

If the above solutions are too complex, you could use raw SQL:

PYTHONCopied
from django.db import connection

with connection.cursor() as cursor:
    cursor.execute("""
        SELECT * FROM (
            SELECT *, ROW_NUMBER() OVER (PARTITION BY name_id ORDER BY updated_at DESC) as rn
            FROM portal
            WHERE customer_id = %s AND customer_id != 0
        ) t
        WHERE rn = 1
    """, [customer_id])
    result = cursor.fetchall()

Recommendation

The most Django-friendly approach would be Option 2, as it uses Django's ORM capabilities while achieving the desired result. However, if performance is critical, Option 3 might be more efficient.