Django- Remove duplicates only if a field is present

Question

I have two models:

class BookSeries(models.Model):
    title = models.CharField(max_length=200, null=False, blank=False, unique=True)
    #extra fields

class Book(models.Model):
    series = models.ForeignKey(BookSeries, blank=True, null=True, default=None)
    publisher = models.ForeignKey(Publisher, default=None, null=True, blank=True)
    title = models.CharField(max_length=200, null=False, blank=False, unique=True)
    #extra fields

Now I want to query all the books which doesn't belong to a series and only one of any of the book which belong to the same series (series can be null).

Problem statement:

I wan to query all the individual books and series. Since a series can have multiple books, and a book may not belong to a series. One of the solutions is to query all the book objects ( which doesn't belong to a series) and query all the series objects as described here. But this would give all series together and books together in the response. I don't want them to be grouped together (I am also using pagination).

something like : Book.objects.filter( disctinct only if(series is not None))

I thought of using distinct and exclude but couldn't make it work.

Answer 1

I would suggest following approach:

1. Get id of all the books which doesn't belong to a series:

   ids_list1 = list(Book.objects.filter(series=None).values_list('id', flat=True))
   

2. Get id of all the books which belongs to a series and get only first using distinct:

   ids_list2 = list(Book.objects
                        .exclude(series=None)   # exclude ones which are not in a series
                        .order_by('series')     # order by series
                        .distinct('series')     # keep the first book in each series
                        .values_list('id', flat=True))
   

Now, you can combine these two lists and make another query to return only the books with these ids:

ids = id_list1 + id_list2
books = Book.objects.filter(id__in=ids)

Answer 2

First exclude all if series is None, then call distinct() return you a list.

Book.objects.exclude(series=None).distinct('series')

If you need to exclude null values and empty strings, the preferred way to do so is to chain together the conditions like so: Book.objects.exclude(series__isnull=True).exclude(series__exact='')

You Can follow this thread for better understanding Filtering for empty or NULL names in a queryset