#28908: Array specific lookups do not work with array aggregates
-------------------------------------+-------------------------------------
Reporter: Jaap Roes | Owner: nobody
Type: New | Status: new
feature |
Component: Database | Version: master
layer (models, ORM) | Keywords: ArrayAgg ArrayField
Severity: Normal | lookup
Triage Stage: | Has patch: 0
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
It's not possible to use array lookups on an array aggregate
(`django.contrib.postgres.aggregates.ArrayAgg`).
Given these models:
{{{#!python
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:
{{{#!python
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:
{{{#!python
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)
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/28908>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to django-updates+unsubscr...@googlegroups.com.
To post to this group, send email to django-updates@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/048.be874a0d3f53611d1eb60c404ec03320%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
