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'. e.g.: `

Bookname,     covertype, origin
The Trial,    hardcover, none
Madam Bovary, hardcover, France

`

Tried many options, annotate, Q, value, subquery, when, case, exists but could come up with a solution.

With sql this is so easy:

select * from books b left join authors a on a.bookref=b.id and a.origin=france where b.covertype='hard' (my models are not books and authors, i picked them because they're django-docs' example models. my models are building and buildingtype, where i want building.id=454523 with buildigtype where buildingtype is active, buildingtype might be null for the building or only 1 active and zero or more passive)

CodePudding user response：

You should use Book id in Auther table.then your query will be like this: Author.objects.filter(origin="france",book__cover_type="hard")

CodePudding user response：

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')

`