[Django] #28908: Array specific lookups do not work with array aggregates
Django
-------------------------------------+-------------------------------------
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.
Django
-------------------------------------+-------------------------------------
Reporter: Jaap Roes | Owner: nobody
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Keywords: ArrayAgg ArrayField | Triage Stage: Accepted
lookup |
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* cc: Sergey Fedoseev (added)
* stage: Unreviewed => Accepted
Comment:
Jaap Roes, would you like to work on this? If not, I could do it.
--
Ticket URL: <https://code.djangoproject.com/ticket/28908#comment:1>
Django
-------------------------------------+-------------------------------------
Reporter: Jaap Roes | Owner: nobody
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Keywords: ArrayAgg ArrayField | Triage Stage: Accepted
lookup |
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (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 :)
--
Ticket URL: <https://code.djangoproject.com/ticket/28908#comment:2>
Django
-------------------------------------+-------------------------------------
| Fedoseev
Type: New feature | Status: assigned
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Keywords: ArrayAgg ArrayField | Triage Stage: Accepted
lookup |
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* status: new => assigned
* owner: nobody => Sergey Fedoseev
--
Ticket URL: <https://code.djangoproject.com/ticket/28908#comment:3>
Django
-------------------------------------+-------------------------------------
| Fedoseev
Type: New feature | Status: assigned
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Keywords: ArrayAgg ArrayField | Triage Stage: Accepted
lookup |
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* has_patch: 0 => 1
Comment:
[https://github.com/django/django/pull/9452 PR]
--
Ticket URL: <https://code.djangoproject.com/ticket/28908#comment:4>
Django
-------------------------------------+-------------------------------------
| Fedoseev
Type: New feature | Status: assigned
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Keywords: ArrayAgg ArrayField | Triage Stage: Accepted
lookup |
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (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.
--
Ticket URL: <https://code.djangoproject.com/ticket/28908#comment:5>
Django
-------------------------------------+-------------------------------------
| Fedoseev
Component: Database layer | Version: master
(models, ORM) |
Keywords: ArrayAgg ArrayField | Triage Stage: Accepted
lookup |
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* status: assigned => closed
* resolution: => fixed
Comment:
In [changeset:"149061103829fb3ad74d050b4ae3cc815b2f451c" 1490611]:
{{{
#!CommitTicketReference repository=""
revision="149061103829fb3ad74d050b4ae3cc815b2f451c"
Fixed #28908 -- Allowed ArrayField lookups on ArrayAgg annotations.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/28908#comment:6>