CODEWITHSUNDEEP
pythondjangodjango-models
Jonathan Adelson
Jonathan Adelson

Reputation: 3321

How do I select from a many-to-many intermediate model in django?

I have models of books and people:

from django.db import models

class Book(models.Model):
    author = models.ManyToManyField('Person')

class Person(models.Model):
    name = models.CharField(max_length=16)

I simplified them a bit here. How do I craft a django query to get all of the authors of the books? With SQL I would do a select on the intermediate table and join it with the people table to get the name, but I'm not sure how to do something similar here... Of course, there are people in the Person table that are not book authors, or I could just get Person.objects.all().

Upvotes: 2

Views: 1740

Answers (4)

jpic
jpic

Reputation: 33420

As easy as 1,2,3 with Filtering on annotations:

from django.db.models import Count

Person.objects.annotate(count_book=Count('book')).filter(count_book__gt=0)

For curiosity, i generated the SQL from each of the ways proposed on this topic:

In [9]: Person.objects.annotate(count_book=Count('book')).filter(count_book__gt=0)
DEBUG (0.000) SELECT "testapp_person"."id", "testapp_person"."name", COUNT("testapp_book_author"."book_id") AS "count_book" FROM "testapp_person" LEFT OUTER JOIN "testapp_book_author" ON ("testapp_person"."id" = "testapp_book_author"."person_id") GROUP BY "testapp_person"."id", "testapp_person"."name", "testapp_person"."id", "testapp_person"."name" HAVING COUNT("testapp_book_author"."book_id") > 0  LIMIT 21; args=(0,)
Out[9]: [<Person: Person object>]

In [10]: Person.objects.exclude(book=None)
DEBUG (0.000) SELECT "testapp_person"."id", "testapp_person"."name" FROM "testapp_person" WHERE NOT (("testapp_person"."id" IN (SELECT U0."id" FROM "testapp_person" U0 LEFT OUTER JOIN "testapp_book_author" U1 ON (U0."id" = U1."person_id") LEFT OUTER JOIN "testapp_book" U2 ON (U1."book_id" = U2."id") WHERE (U2."id" IS NULL AND U0."id" IS NOT NULL)) AND "testapp_person"."id" IS NOT NULL)) LIMIT 21; args=()
Out[10]: [<Person: Person object>]

In [11]: Person.objects.filter(pk__in=Book.objects.values_list('author').distinct())
DEBUG (0.000) SELECT "testapp_person"."id", "testapp_person"."name" FROM "testapp_person" WHERE "testapp_person"."id" IN (SELECT DISTINCT U1."person_id" FROM "testapp_book" U0 LEFT OUTER JOIN "testapp_book_author" U1 ON (U0."id" = U1."book_id")) LIMIT 21; args=()
Out[11]: [<Person: Person object>]

Maybe this can help you choose.

Personnaly, i prefer the version by Chris because it is the shortest. On the other hand, I don't know for sure about the impact of having subqueries which is the case for the two other ways. That said, they do demonstrate interresting QuerySet concepts:

  1. Annonation, is aggregation per value of the queryset. If you use aggregate(Count('book')) then you will get the total number of books. If you use annotate(Count('book')) then you get a total number of book per value of the queryset (per Person). Also, each person has a 'count_book' attribute which is pretty cool: Person.objects.annotate(count_book=Count('book')).filter(count_book__gt=0)[0].count_book

  2. Subqueries, very useful to create complex queries or optimize queries (merge querysets, generic relation prefetching for example).

Upvotes: 3

Chris Pratt
Chris Pratt

Reputation: 239290

Easiest way:

Person.objects.filter(book__isnull=False)

That will select all people that have at least one book associated with them.

Upvotes: 3

dm03514
dm03514

Reputation: 55962

You can get all the ID's of the authors quite easily using Book.objects.all().values_list('author', flat=True).distinct()

As jpic points out below

Person.objects.filter(pk__in=Book.objects.values_list('author').distinct()) will give you all the person objects and not just their id's.

Upvotes: 1

keni
keni

Reputation: 1768

This chapter of the django book answers all your model needs with examples very similar to yours, including a ManyToMany relation. http://www.djangobook.com/en/2.0/chapter10/

Upvotes: -1

Related Questions