0
\$\begingroup\$

I have a Book model and an Author model that has a FK relationship to Book (I realize that it should be the other way around, but I had to change the names of the real items to Book and Author for proprietary reasons.) Related name on this relationship is authors.

I am trying to find all Books that do not have an Author or Authors, except for an exclude list of tuples called BOOKS_WITH_NO_AUTHORS that is about 25 items long and unlikely to grow much. The pair of items in the tuples are unique together. I can change this list of tuples to a dictionary or other format if that is helpful.

Everything works but it is very slow. The database of books and authors is large.

BOOKS_WITH_NO_AUTHORS = [(1979, "ABC123"), (1928, "DEF456")]  

def missing_author(request):

    excluded_books = [Book.objects.get(publish_year=book[0], book_num=book[1]).book_id
                      for book in BOOKS_WITH_NO_AUTHORS]

    books_missing_authors = Book.objects. \
        filter(authors=None). \
        exclude(book_id__in=excluded_books). \
        order_by('publish_year')

    return render(request,
                  'missing_author.html',
                  {"data": books_missing_authors})
\$\endgroup\$
3
\$\begingroup\$

One of the inefficiencies in your code is when you're collecting the list of ids of excluded_books. If you have 25 BOOKS_WITH_NO_AUTHORS, you're hitting the database 25 times.

I would recommend using the Q objects. https://docs.djangoproject.com/en/1.11/topics/db/queries/#complex-lookups-with-q-objects

This should substantially speed it up:

from django.db.models import Q


BOOKS_WITH_NO_AUTHORS = [(1979, "ABC123"), (1928, "DEF456")]

def missing_author(request):

    # Turn list of BOOKS_WITH_NO_AUTHORS into list of Q objects
    queries = [Q(publish_year=publish_year, book_num=book_num) for publish_year, book_num in BOOKS_WITH_NO_AUTHORS]

    # Take one Q object from the list
    query = queries.pop()

    # Or the Q object with the ones remaining in the list
    for item in queries:
        query |= item

    books_missing_authors = Book.objects. \
        filter(authors=None). \
        exclude(query). \
        order_by('publish_year')

    return render(request,
                  'missing_author.html',
                  {"data": books_missing_authors})
\$\endgroup\$
  • \$\begingroup\$ Still slow, but faster! Thanks! \$\endgroup\$ – thumbtackthief Jul 26 '17 at 15:36
  • \$\begingroup\$ Slow? Add index on publish_year, book_num \$\endgroup\$ – Tomasz Jakub Rup Aug 15 '17 at 9:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.