Search code examples
sqldjangosqlitedjango-orm

Django ORM .distinct() seems to return duplicate results

I have an Event model that represents an Event happening. I have another EventShow model which represents a date and time that the event is taking place. Each event can have multiple shows, so I have a ForeignKey from EventShow to Event as follows -

class Event(models.Model):
    name = models.CharField(max_length=32)
    # ... and more

class EventShow(models.Model):
    event = models.ForeignKey(Event, on_delete=models.CASCADE, related_name='shows')
    start_date = models.DateTimeField()

I want to get a list of events which have at least one show happening in the future, so in my view I'm calling the ORM like this -

events = Event.objects.filter(shows__start_date__gt=now()).prefetch_related('shows').distinct().order_by('shows__start_date')

However, I still get the same event showing up multiple times if it has multiple shows. How can I fix this?

I looked at the SQL that was being generated by this call -

SELECT DISTINCT "core_event"."id", "core_event"."name", "core_eventshow"."start_date" 
  FROM "core_event" 
    INNER JOIN "core_eventshow" ON ("core_event"."id" = "core_eventshow"."event_id") 
  WHERE ("core_eventshow"."start_date" > 2023-08-16 02:48:46.063093) 
  ORDER BY "core_eventshow"."start_date" ASC

I'm using SQLite for development.

Solution

  • The problem is you have the "core_eventshow"."start_date" in your SELECT clause because of your .order_by. This causes every row selected to be distinct from each other.

    Removing the .order_by should fix this as it doesn't serve any useful purpose anyway.

    Alternatively you can use PostgreSQL with its DISTINCT ON support:

    On PostgreSQL only, you can pass positional arguments (*fields) in order to specify the names of fields to which the DISTINCT should apply. This translates to a SELECT DISTINCT ON SQL query. Here’s the difference. For a normal distinct() call, the database compares each field in each row when determining which rows are distinct. For a distinct() call with specified field names, the database will only compare the specified field names.