Opened 12 years ago

Closed 12 years ago

#17585 closed New feature (worksforme)

Not possible to filter authors that have books other than hardcover

Reported by: mbertheau@… Owned by: nobody
Component: Database layer (models, ORM) Version: 1.3
Severity: Normal Keywords:
Cc: mbertheau@…, anssi.kaariainen@… Triage Stage: Unreviewed
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

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.

Change History (1)

comment:1 by Anssi Kääriäinen, 12 years ago

Cc: anssi.kaariainen@… added
Resolution: worksforme
Status: newclosed

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.

Note: See TracTickets for help on using tickets.
Back to Top