How to query django m2m for a single instance that satisfies multiple requirements?

Question

For the sake of example, I have the following Django models:

class Author(models.Model):
    first_name = models.CharField(max_length=30)
    last_name = models.CharField(max_length=30)

class Book(models.Model):
    name = models.CharField(max_length=30)
    authors = models.ManyToManyField(Author)

(because books regularly have multiple authors, and authors regularly write multiple books)

Now say I have a list of authors' names as tuples:

author_list = [('clarke', 'arthur'), ('herbert', 'frank'), ('asimov', 'isaac')]

Is there a way to query for all Books whose authors m2m field has at least one Author instance that satisfies

author.last_name = pair[0]
author.first_name = pair[1]

for some pair in author_list? I.e. to get all books contributed to by at least one of a given list of authors?

The query I *want* to write looks like this:

Book.objects.filter(
    authors__first_name__in=[pair[1] for pair in author_list],
    authors__last_name__in=[pair[0] for pair in author_list]
)

but that doesn't work, because then all permutations (Arthur Herbert, Frank Asimov, Isaac Clarke, etc...) are allowed. But I'm not sure how to say, essentially:

'Select all books who have an author that satisfies these two things at once.'

or really:

'Select all books who have an author whose pair of (last_name, first_name) is in this list of tuples.'

Is there a clean way to do that search in django?

Try using ``Q`` object. You may concatenate 2 Q objects with ``|`` or ``&``. — user996142, Oct 22 '15 at 20:50

score 1 · Answer 1 · edited May 23 '17 at 11:44

I think you could build a query using q objects

Q(last_name=pair[0], first_name=pair[1]) | Q(last_name=pair2[0], first_name=pair2[1])

The above query should generate get books with last name clark AND first name arthur OR last_name herbert and first_name frank.

There are quite a few answers on how to do this dynamically:

How to dynamically compose an OR query filter in Django?

You will probably have to twiddle with the query and inspect the query django generates to verify that it is what you intend

score 1 · Answer 2 · answered Oct 22 '15 at 20:51

Ok I think this works:

query = []
for last_name, first_name in author_list:
    query.append(Q(authors__last_name=last_name, authors__first_name=first_name))
query = reduce(operator.or_, query)
Book.objects.filter(query)

but please still answer if you see something better!

score 1 · Accepted Answer · edited Oct 23 '15 at 18:42

1

You can build a query using Q objects.

Based on your answer, but without using reduce and operator:

query = Q()
for last_name, first_name in author_list:
    Q |= Q(authors__last_name=last_name, authors__first_name=first_name)
Book.objects.filter(query)

edited Oct 23 '15 at 18:42

Brendan W

3,303
3
18
37

answered Oct 23 '15 at 01:03

vsd

1,473
13
11

oo that's awesome I didn't know `|=` is a thing! – Brendan W Oct 23 '15 at 02:12

How to query django m2m for a single instance that satisfies multiple requirements?

3 Answers3