23

I have a query that requires to filter exactly 2 authors with the ID

Theoretically,

Book.objects.filter(author__id=1, author__id=2).

which is not possible.

How can I solve this problem?

Cheers, Mickey

Serjik
  • 10,543
  • 8
  • 61
  • 70
Mickey Cheong
  • 2,980
  • 6
  • 38
  • 50

4 Answers4

51

Not intuitive at first but the answer is right in front of us.

Book.objects.filter(author__id=1).filter(author__id=2)

If you want an exact match, you could potentially further filter this result by those items that only have exactly 2 authors.

Book.objects.annotate(count=Count('author')).filter(author__id=1)\
                .filter(author__id=13).filter(count=2)

If you want exact matches dynamically, how about something like this?:

def get_exact_match(model_class, m2m_field, ids):
    query = model_class.objects.annotate(count=Count(m2m_field))\
                .filter(count=len(ids))
    for _id in ids:
        query = query.filter(**{m2m_field: _id})
    return query

matches = get_exact_match(MyModel, 'my_m2m_field', [1, 2, 3, 4])

# matches is still an unevaluated queryset, so you could run more filters
# without hitting the database.
Yuji 'Tomita' Tomita
  • 115,817
  • 29
  • 282
  • 245
  • hmmm, what if i have a list... how can I dynamically do it? i was using filters={} and Book.objects.filter(**filters) – Mickey Cheong Mar 14 '11 at 17:46
  • [ignore the previous one] hmmm, what if i have a list of Author IDs... authors = [1,2,3,4] how can I dynamically do it? i was using filters={} and Book.objects.filter(**filters) – Mickey Cheong Mar 14 '11 at 17:52
  • A dict can't have 2 keys of the same name, so that's not really possible without setting up a special way to handle your dict. Further complications when you have to determine which filters are chained separately and which are not.. If you're always looking for an exact match, you could pass in a list of items. Let me try writing an example. – Yuji 'Tomita' Tomita Mar 14 '11 at 17:52
  • thank you, it works. what's the difference between having a annotate and without? – Mickey Cheong Mar 15 '11 at 01:29
  • 3
    If you don't annotate, the match is not exclusive. Book with authors [1, 2, 3] would match a search for a book with authors [1, 2]. If you'd like that, you can just remove the annotate and first filter. – Yuji 'Tomita' Tomita Mar 15 '11 at 02:02
  • 1
    @Yuji'Tomita'Tomita I tried this solution, but for me for _id in ids: query.filter(id=_id) return query, it returns an empty queryset. I've tried it several times and it's the same thing. – deadlock Jan 30 '14 at 23:34
  • 2
    There is an error in the code - the query should be as follows: `query = query.filter(**{m2m_field: _id})` – rrauenza May 28 '15 at 15:59
  • @rrauenzanice find. Updated! – Yuji 'Tomita' Tomita Feb 19 '16 at 21:31
  • Nested `filter()` adds multiple joins to the same table, I would rather use `query.filter(m2m_field__in=ids)` – Dmitry Mugtasimov Jun 29 '19 at 09:41
1

New questions are pointing to this one as a duplicate, so here is an updated answer (for one specific backend).

If the backend is Postgres, the SQL you want is (assuming the M2M table is called bookauthor):

SELECT *
FROM book
WHERE
    (SELECT ARRAY_AGG(bookauthor.author_id)
     FROM bookauthor
     WHERE bookauthor.book_id = book.id) = Array[1, 2];

You can get Django to generate nearly this SQL.

First, pip install django-sql-utils. Then create this Array class:

from django.db.models import Func

class Array(Func):
    function = 'ARRAY'
    template = '%(function)s[%(expressions)s]'

And now you can write your ORM queryset:

from sql_util.utils import SubqueryAggregate
from django.contrib.postgres.aggregates import ArrayAgg

books = Book.objects.annotate(
            author_ids=SubqueryAggregate('author__id', Aggregate=ArrayAgg)
        ).filter(author_ids=Array(1, 2))
Brad Martsberger
  • 1,747
  • 13
  • 7
-3

Q objects will help you. Docs

Book.objects.filter(Q(author__id=1) & Q(author__id=2))
H1D
  • 748
  • 1
  • 6
  • 18
-6

You can use an "IN" query. Django Docs

Book.objects.filter(author__id__in=[1,2])

RyanBrady
  • 6,633
  • 4
  • 27
  • 32