#28908 closed New feature (fixed)

Array specific lookups do not work with array aggregates

Reported by: Jaap Roes Owned by: Sergey Fedoseev
Component: Database layer (models, ORM) Version: master
Severity: Normal Keywords: ArrayAgg ArrayField lookup
Cc: Sergey Fedoseev Triage Stage: Accepted
Has patch: yes Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no


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):
        self.novel = Novel.objects.create(
            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):
        self.book = Book.objects.create(title='A Christmas Carol')

    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)

Change History (6)

comment:1 in reply to:  description Changed 10 months ago by Sergey Fedoseev

Cc: Sergey Fedoseev added
Triage Stage: UnreviewedAccepted

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

comment:2 Changed 10 months ago by 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.

Last edited 10 months ago by Jaap Roes (previous) (diff)

comment:3 Changed 10 months ago by Sergey Fedoseev

Owner: changed from nobody to Sergey Fedoseev
Status: newassigned

comment:4 Changed 10 months ago by Sergey Fedoseev

Has patch: set

comment:5 Changed 10 months ago by 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.

comment:6 Changed 10 months ago by Tim Graham <timograham@…>

Resolution: fixed
Status: assignedclosed

In 1490611:

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

Note: See TracTickets for help on using tickets.
Back to Top