Django query with filtered annotations from related table
Take books and authors models for example with books having one or more authors. Books having cover_type and authors having country as origin.
How can I list all the books with hard cover, and authors only if they're from from france?
Books.objects.filter(cover_type='hard', authors__origin='france')
This query doesnt retrieve books with hard cover but no french author.
I want all the books with hard cover, this is predicate #1. And if their authors are from France, I want them annotated, otherwise authors field may be empty or 'None'.
Tried many options, annotate, Q, value, subquery, when, case, exists but could come up with a solution.
You should use Book id in Auther table.then your query will be like this: Author.objects.filter(origin="france",book__cover_type="hard")
I think i solved it with subquery, outerref, exists, case, when, charfield...too many imports for a simple sql. `
author = Authors.objects.filter(bookref=OuterRef('id'), origin='France').values('origin')
books = Books.objects.filter(cover_type='hard').annotate(author=Case(When(Exists(author), then=Subquery(author)), default='none', output_field=CharField())).distinct().values('name','cover_type','author')
`