Django Model - Get distinct value list

Question

I try to get a list of distinct foreign keys and I wrote this:

my_ids = Entity.objects.values('foreign_key').distinct()

But I get just a list of UNDISTINCT foreign keys... What am I missing?

Thanks!

Answer 1

Passing an argument to distinct doesn't work for MySQL-databases (AFAIK)

This one works and returns just one object:

Entity.objects.order_by('foreign_key').values('foreign_key').distinct()

Answer 2

Perhaps you might want to go with this:

Entity.objects.order_by().values_list('foreign_key', flat=True).distinct()

Answer 3

Entity.objects.values_list('foreign_key', flat=True).distinct().order_by()

distinct not work without order_by, as explained in Django documentation:

Any fields used in an order_by() call are included in the SQL SELECT columns. This can sometimes lead to unexpected results when used in conjunction with distinct(). If you order by fields from a related model, those fields will be added to the selected columns and they may make otherwise duplicate rows appear to be distinct. Since the extra columns don’t appear in the returned results (they are only there to support ordering), it sometimes looks like non-distinct results are being returned.

Similarly, if you use a values() query to restrict the columns selected, the columns used in any order_by() (or default model ordering) will still be involved and may affect uniqueness of the results.

The moral here is that if you are using distinct() be careful about ordering by related models. Similarly, when using distinct() and values() together, be careful when ordering by fields not in the values() call.