Array specific lookups do not work with array aggregates

[Jaap Roes]

It's not possible to use array lookups on an array aggregate (django.contrib.postgres.aggregates.ArrayAgg).

Given these models:

import uuid

from django.contrib.postgres.fields import ArrayField
from django.db import models

class Publisher(models.Model):
    name = models.CharField(max_length=100, unique=True)

class Author(models.Model):
    id = models.UUIDField(default=uuid.uuid4, primary_key=True)
    name = models.CharField(max_length=100)

class Genre(models.Model):
    code = models.DecimalField(max_digits=3, decimal_places=1, primary_key=True)
    name = models.CharField(max_length=100)

class Book(models.Model):
    title = models.CharField(max_length=100)
    publishers = models.ManyToManyField(Publisher)
    authors = models.ManyToManyField(Author)
    genres = models.ManyToManyField(Genre)

class Novel(models.Model):
    title = models.CharField(max_length=100)
    publishers = ArrayField(models.CharField(max_length=100))
    authors = ArrayField(models.UUIDField())
    genres = ArrayField(models.DecimalField(max_digits=3, decimal_places=1))

These tests pass:

from django.contrib.postgres.aggregates import ArrayAgg
from django.test import TestCase

from .models import Author, Book, Genre, Novel, Publisher

class BaseArrayTestCase:
    def setUp(self):
        self.author = Author.objects.create(name='Charles Dickens')
        self.publisher = Publisher.objects.create(name='Chapman & Hall')
        self.genre = Genre.objects.create(code='1.0', name='Novella')

class TestNovelArrayField(BaseArrayTestCase, TestCase):
    def setUp(self):
        super().setUp()
        self.novel = Novel.objects.create(
            authors=[self.author.id],
            publishers=[self.publisher.name],
            genres=[self.genre.code],
            title='A Christmas Carol')

    def test_overlap(self):
        qs = Novel.objects.filter(authors__overlap=[self.author.id])
        # SELECT * FROM novel WHERE novel.authors && '{b1824cde-011d-46ec-80f9-8139a20bdacb}'::uuid[];
        self.assertEqual(qs.get(), self.novel)

    def test_contains(self):
        qs = Novel.objects.filter(genres__contains=[self.genre.code])
        # SELECT * FROM novel WHERE novel.genres @> '{1.0}'::numeric(3, 1)[];
        self.assertEqual(qs.get(), self.novel)

    def test_contained_by(self):
        qs = Novel.objects.filter(publishers__contained_by=[self.publisher.name])
        # SELECT * FROM novel WHERE novel.publishers <@ '{Chapman & Hall}'::varchar(100)[];
        self.assertEqual(qs.get(), self.novel)

    def test_len(self):
        qs = Novel.objects.filter(authors__len=1)
        # SELECT * FROM novel WHERE array_length(novel.authors, 1) = 1;
        self.assertEqual(qs.get(), self.novel)

While these tests fail:

class TestBookArrayAgg(BaseArrayTestCase, TestCase):
    def setUp(self):
        super().setUp()
        self.book = Book.objects.create(title='A Christmas Carol')
        self.book.authors.set([self.author])
        self.book.publishers.set([self.publisher])
        self.book.genres.set([self.genre])

    def test_overlap(self):
        qs = Book.objects.annotate(author_ids=ArrayAgg('authors__id'))
        qs = qs.filter(author_ids__overlap=[self.author.id])
        # Expected:
        #   SELECT book.*, ARRAY_AGG(book_authors.author_id) AS author_ids
        #   FROM book
        #   LEFT OUTER JOIN book_authors ON (book.id = book_authors.book_id)
        #   GROUP BY book.id
        #   HAVING ARRAY_AGG(book_authors.author_id) && '{b1824cde-011d-46ec-80f9-8139a20bdacb}'::uuid[];

        # Actual:
        #   Unsupported lookup 'overlap' for UUIDField or join on the field not permitted.
        self.assertEqual(qs.get(), self.book)

    def test_contains(self):
        qs = Book.objects.annotate(genre_codes=ArrayAgg('genres__code'))
        qs = qs.filter(genre_codes__contains=[self.genre.code])
        # Expected:
        #   SELECT book.*, ARRAY_AGG(book_genres.genre_id) AS genre_codes
        #   FROM book
        #   LEFT OUTER JOIN book_genres ON (book.id = book_genres.book_id)
        #   GROUP BY book.id
        #   HAVING ARRAY_AGG(book_genres.genre_id) @> '{1.0}'::numeric(3, 1)[];

        # Actual:
        #   SELECT
        #     ...
        #   HAVING ARRAY_AGG(book_genres.genre_id)::text LIKE '%["1.0"]%';
        self.assertEqual(qs.get(), self.book)

    def test_contained_by(self):
        qs = Book.objects.annotate(publisher_names=ArrayAgg('publishers__name'))
        qs = qs.filter(publisher_names__contained_by=[self.publisher.name])
        # Expected:
        #   SELECT book.*, ARRAY_AGG(publisher.name) AS publisher_names
        #   FROM book
        #   LEFT OUTER JOIN book_publishers ON (book.id = book_publishers.book_id)
        #   LEFT OUTER JOIN publisher ON (book_publishers.publisher_id = publisher.id)
        #   GROUP BY book.id
        #   HAVING ARRAY_AGG(publisher.name) <@ '{Chapman & Hall}'::varchar(100)[];

        # Actual:
        #   Unsupported lookup 'contained_by' for CharField or join on the field not permitted.
        self.assertEqual(qs.get(), self.book)

    def test_len(self):
        qs = Book.objects.annotate(author_names=ArrayAgg('authors__name'))
        qs = qs.filter(author_names__len=1)
        # Expected:
        #  SELECT book.*, ARRAY_AGG(author.name) AS author_names
        #  FROM book
        #  LEFT OUTER JOIN book_authors ON (book.id = book_authors.book_id)
        #  LEFT OUTER JOIN author ON (book_authors.author_id = author.id)
        #  GROUP BY book.id
        #  HAVING array_length(ARRAY_AGG(author.name), 1) = 1;

        # Actual:
        #  Unsupported lookup 'len' for CharField or join on the field not permitted.
        self.assertEqual(qs.get(), self.book)

[Sergey Fedoseev]

Jaap Roes, would you like to work on this? If not, I could do it.

[Jaap Roes]

I'd much appreciate it if you work on it Sergey, I have no intentions to dive into the ORM at this moment in time :).

edit: You probably need to be aware of this issue: #28038, which might complicate this a little bit.

[Sergey Fedoseev]

​PR

[Jaap Roes]

I ran the tests in this ticket on Sergey's branch and they all pass now, so lgtm. This new feature should probably be documented somewhere.

[Tim Graham <timograham@…>]

In 1490611:

Fixed #28908 -- Allowed ArrayField lookups on ArrayAgg annotations.