Not possible to filter authors that have books other than hardcover

[mbertheau@…]

It seems that the Django ORM cannot express in one query

SELECT a.* FROM author AS a JOIN book AS b ON b.author_id = a.id WHERE b.bound <> 'hardcover' GROUP BY a.id

that is all authors that have at least one book that is not hardcover.

Both exclude(bookbound='hardcover') and filter(~Q(bookbound='hardcover')) leave only authors that had no hardcover books at all.

A field lookup 'not equal' could be provided with that meaning. Alternatively, the meaning of filter(~Q(bookbound='hardcover')) could be changed.

[Anssi Kääriäinen]

Actually, I think it can:

Author.objects.all().delete()
a = Author.objects.create(name='anssi')
b = Author.objects.create(name='bart')
c = Author.objects.create(name='carl')

book1 = Book.objects.create(bound='hardcover', author=a)
book2 = Book.objects.create(bound='somethingelse', author=a)
book3 = Book.objects.create(bound='somethingelse', author=b)
print Author.objects.filter(pk__in=Book.objects.filter(~Q(bound='hardcover')).values_list('author_id'))
[<Author: anssi>, <Author: bart>]
print Author.objects.filter(pk__in=Book.objects.filter(~Q(bound='hardcover')).values_list('author_id')).query
SELECT "tests_author"."id", "tests_author"."name" FROM "tests_author" WHERE "tests_author"."id" IN (SELECT U0."author_id" FROM "tests_book" U0 WHERE NOT (U0."bound" = hardcover ))

Or, at least I hope that is what you wanted. The .exclude() can't do what you want, it is defined to remove all authors that have any hardcovers. I admit that the above query isn't the easiest to discover. This could maybe be documented, but I don't think that is a good idea, as:

• finding this type of query from the documentation is hard.
• especially as other queries like this would then logically need to be added, too.

Please reopen if I am mistaken, or you disagree with my reasoning about documenting this.