Trying to make a PostgreSQL field with a list of foreign keys in Django

Question

Here is what I'm trying to do: Make a model in Django that is a PostgreSQL array (database specific type), which contains foreign keys to another model.

class Books(models.Model):
    authors = ArrayField(
        models.ForeignKey('my_app.Authors', default=None, null=True, blank=True),
        blank=True,
        default=list()
    )

When I try to makemigrations, Django gives me this error:

SystemCheckError: System check identified some issues:
ERRORS:
my_app.Books.authors: (postgres.E002) Base field for array cannot be a related field.

Any Ideas on how to beat that?

Answer 1

You can't create an array of foreign keys. It is not a Django limitation, it is a PostgreSQL "limitation".

The reason is that a foreign key is not a type, it is a constraint on a field. An array of foreign keys does not make any sense.

The general approach to achieve this is to use an intermediate table that will be used as a link between two others :

Authors(id, name)
Books(id, title, pub_date)
BookAuthors(id, book_id, author_id)

In the above exemple, BookAuthors.book_id is a foreign key to Books.id and BookAuthors.author_id is a foreign key to Authors.id. Thus, the table BookAuthors is used to match an author with a book and vice versa.

Django abstracts this intermediate table with ManyToManyField fields:

class Authors(models.Model):
    name = models.CharField(...)

class Books(models.Model):
    title = models.CharField(...)
    pub_date = models.DateField(...)
    authors = models.ManyToManyField('my_app.Authors',
                                     related_name='authored_books')

Behind the scenes, Django will create the intermediate table.

Then, you can get all authors of a book using book.authors.all(), or all books authored by an author using author.authored_books.all().