Opened 7 years ago
Closed 7 years ago
#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: | dev |
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 |
Description
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)
Change History (6)
comment:1 by , 7 years ago
Cc: | added |
---|---|
Triage Stage: | Unreviewed → Accepted |
comment:2 by , 7 years ago
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.
comment:3 by , 7 years ago
Owner: | changed from | to
---|---|
Status: | new → assigned |
comment:5 by , 7 years ago
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.
Jaap Roes, would you like to work on this? If not, I could do it.