#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 [email protected].
To post to this group, send email to [email protected].
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.
