Django query returns the same objects twice

Question

Generic example. When I do a query like this

a = MyObject.objects.filter(...)

a will contain everything matched by the filter. When i then do:

a.filter(...)

a will contain only the objects that matched both filters, right? So after the first filter(), I might have 5 elements, and then after the second filter() I might have 2 of those 5 left?

I am getting weird results with one of my queries. The following query:

>>> Item.objects.filter(account__in=Account.objects.filter(customer=a), disabled=False)
[<Item: PSN-100396>]

This yields one result. Keep in mind that 100396 is the primary key, so there can only be one Item with this idnetifier. Now when I apply an additional filter that should show only those Items that are on a disabled Invoice or no Invoice:

>>> Item.objects.filter(account__in=Account.objects.filter(customer=a), disabled=False).filter(Q(iteminv__invoice__disabled=False) | Q(iteminv__isnull=True))
[<Item: PSN-100396>, <Item: PSN-100396>]

I get this result with the same object twice... How is this possible? Did i misunderstand something?

Putting it all in one filter() produces the same weird output:

>>> Item.objects.filter(Q(account__in=Account.objects.filter(customer=a)), Q(disabled=False), Q(iteminv__invoice__disabled=False) | Q(iteminv__isnull=True))
[<Item: PSN-100396>, <Item: PSN-100396>]

Django version 1.6.2

Answer 1

Due to the joins involved when filtering on many-to-many relations or reverse foreign key relations instances can appear twice in the result set (In this cases Django will mostly applay an INNER JOIN which can produce duplicates). Therefore you should use distinct() on your queryset. (The joins in the sql query make an Item appear in the table for every relation it has with Account.)

See also the examples in the django documentation for filtering on many to many relations.

EDIT: If you want to play with the SQL/and or examine the generated query, do something like print Item.objects.filter(your_query).query.