Remove objects with duplicated values from Django queryset

Question

I want to remove objects with the same field value from a queryset. I've seen some related answers here, but none of them use the annotate and Min feature and I don't know if that approach is even possible.

Imagine I have a 'Book' model with a 'Library' model FK. A Library could have multiple Books with the same title.

How can I obtain a list of Books from that Library with different titles and lower price?

I've tried different approaches with no success:

models.py

class Library(models.Model):
    name = models.CharField(_('Library name'), max_length=255)
    location = models.ForeignKey(Location, on_delete=models.CASCADE)

class Book(models.Model):
    name = models.CharField(_('Title'), max_length=255)
    author = models.CharField(_('Author'), max_length=255)
    editorial = models.CharField(_('Editorial'), max_length=255)
    price = models.FloatField(_('Price'), default=0)
    library = models.ForeignKey(Library, on_delete=models.CASCADE, related_name='libraries',
                                related_query_name='libraries')

Queryset

Book.objects.annotate(count_id=Count('title'), min_price=Min('price')).filter(library__id=21, price=F('min_price')).values_list('title')

Example:

Having this objects | ID | Title | Price | |:---- |:------:| :-----| | 1 | Trainspotting | 3 | | 2 | The Catcher in the rye | 2 | | 3 | Trainspotting | 1 | | 4 | Lord of the Rings | 5 | | 5 | Trainspotting | 5 |

I want to obtain the following queryset:

ID	Title	Price
2	The Catcher in the rye	2
3	Trainspotting	1
4	Lord of the Rings	5

EDIT:

Using the distinct=True in Count gives me the following results:

In this case my Library contains two Trainspotting books with the same price and two titles of 'The Catcher in the Rye' with prices 2 and 4.

Here I'm printing the annotated fields within the name to see what's could be wrong: Book.objects.annotate(count_id=Count('title', distinct=True), min_price=Min('price')).filter(library__id=21, price=F('min_price')).values_list('name', 'min_price', 'count_id')

The QS I obtain is the following:

<QuerySet [('Trainspotting', 1.0, 1), ('Trainspotting', 1.0, 1), ('The Catcher in the Rye', 2.0, 1), ('The Catcher in the Rye', 4.0, 1)]>

Thanks you very much for your help!

Brian Destura · Accepted Answer · 2021-06-28 01:46:12Z

1

You can do:

Book.objects.values('title').annotate(count=Count('title'), price=Min('price'))

This will return you the count of titles per title, and also the minimum price per title.

You need to pay attention to the order of annotate and values. You can have a read here for a detailed explanation: https://docs.djangoproject.com/en/3.2/topics/db/aggregation/#order-of-annotate-and-values-clauses

edited Jun 28, 2021 at 1:46

answered Jun 28, 2021 at 1:40

Brian Destura

12.1k3 gold badges22 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Wood Pecker Over a year ago

I'm afraid it wont work in my case, because I'm using an SQLite db. But I really appreciate your answer and I'm sure it will help someone. Thank you!

Brian Destura Over a year ago

Just curious, why won't it work on SQLite?

Wood Pecker Over a year ago

I've just tried your answer and I works like charm. Thank you very much! I had the belief that annotations only works with PostgreSQL and recently with MySQL db backends. So sorry for that my friend!

Collectives™ on Stack Overflow

Remove objects with duplicated values from Django queryset

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related