#26056 closed New feature (fixed)
ArrayField does not work with ValueListQuerySet
Reported by: | Przemek | Owned by: | bcail |
---|---|---|---|
Component: | contrib.postgres | Version: | dev |
Severity: | Normal | Keywords: | |
Cc: | Triage Stage: | Ready for checkin | |
Has patch: | yes | Needs documentation: | no |
Needs tests: | no | Patch needs improvement: | no |
Easy pickings: | no | UI/UX: | no |
Description (last modified by )
Basically queries of type:
A.objects.filter(array_field__overlap=B.objects.filter(foo).values_list('id', flat=True))
fail at Python level:
Traceback (most recent call last): File "failing.py", line 9, in <module> UserList.objects.filter(users__overlap=User.objects.all().values_list('id', flat=True)).count() File "/home/przemek/.virtualenvs/italamo/lib/python2.7/site-packages/Django-1.8.5-py2.7.egg/django/db/models/query.py", line 318, in count return self.query.get_count(using=self.db) File "/home/przemek/.virtualenvs/italamo/lib/python2.7/site-packages/Django-1.8.5-py2.7.egg/django/db/models/sql/query.py", line 466, in get_count number = obj.get_aggregation(using, ['__count'])['__count'] File "/home/przemek/.virtualenvs/italamo/lib/python2.7/site-packages/Django-1.8.5-py2.7.egg/django/db/models/sql/query.py", line 447, in get_aggregation result = compiler.execute_sql(SINGLE) File "/home/przemek/.virtualenvs/italamo/lib/python2.7/site-packages/Django-1.8.5-py2.7.egg/django/db/models/sql/compiler.py", line 829, in execute_sql sql, params = self.as_sql() File "/home/przemek/.virtualenvs/italamo/lib/python2.7/site-packages/Django-1.8.5-py2.7.egg/django/db/models/sql/compiler.py", line 387, in as_sql where, w_params = self.compile(self.query.where) File "/home/przemek/.virtualenvs/italamo/lib/python2.7/site-packages/Django-1.8.5-py2.7.egg/django/db/models/sql/compiler.py", line 357, in compile sql, params = node.as_sql(self, self.connection) File "/home/przemek/.virtualenvs/italamo/lib/python2.7/site-packages/Django-1.8.5-py2.7.egg/django/db/models/sql/where.py", line 104, in as_sql sql, params = compiler.compile(child) File "/home/przemek/.virtualenvs/italamo/lib/python2.7/site-packages/Django-1.8.5-py2.7.egg/django/db/models/sql/compiler.py", line 357, in compile sql, params = node.as_sql(self, self.connection) File "/home/przemek/.virtualenvs/italamo/lib/python2.7/site-packages/Django-1.8.5-py2.7.egg/django/contrib/postgres/fields/array.py", line 183, in as_sql sql, params = super(ArrayOverlap, self).as_sql(qn, connection) File "/home/przemek/.virtualenvs/italamo/lib/python2.7/site-packages/Django-1.8.5-py2.7.egg/django/contrib/postgres/lookups.py", line 8, in as_sql params = lhs_params + rhs_params TypeError: can only concatenate list (not "tuple") to list
Toy project to reproduce this behavior can be found here: https://github.com/CGenie/django_array_join_fail
This fails in 1.8 as well as in 1.9.
Change History (18)
comment:1 by , 9 years ago
Triage Stage: | Unreviewed → Accepted |
---|---|
Type: | Bug → New feature |
Version: | 1.9 → master |
comment:2 by , 9 years ago
Description: | modified (diff) |
---|
comment:3 by , 8 years ago
Has patch: | set |
---|---|
Needs documentation: | set |
PR it follows the suggestion to wrap a query inside array
. Needs documentation is marked, since the release notes need to be updated, and probably also a note in the documentation itself.
comment:4 by , 8 years ago
Patch needs improvement: | set |
---|
comment:5 by , 8 years ago
Needs documentation: | unset |
---|
It has been reworked but I suppose other lookups in django.contrib.postgres
will want a similar functionality.
comment:6 by , 8 years ago
Does the new Subquery functionality in Django 1.11 allow for a similar result? E.g. would this work?:
qs_b = B.objects.filter(foo) A.objects.filter(array_field__overlap=Subquery(qs_b.values_list('id', flat=True)))
EDIT: This approach does not work.
comment:7 by , 7 years ago
I posted solution here: https://github.com/django/django/pull/7838#issuecomment-337223376
It's Array with Subquery, just pure subquery isn't enough.
comment:8 by , 3 years ago
For anyone who ends up searching for this, the failure (as of 2.2 and possibly earlier) is a ProgrammingError
from the database rather than the TypeError
mentioned above.
The workaround mentioned in https://github.com/django/django/pull/7838#issuecomment-337223376 continues to work:
A.objects.filter( array_field__overlap=models.Func( B.objects.values('pk'), function='array', ), )
comment:9 by , 3 years ago
comment:10 by , 2 years ago
@Simon, were you thinking something like this (modified from the PR)?
diff --git a/django/contrib/postgres/lookups.py b/django/contrib/postgres/lookups.py index f2f88ebc0a..efb2fb6b12 100644 --- a/django/contrib/postgres/lookups.py +++ b/django/contrib/postgres/lookups.py @@ -1,4 +1,5 @@ from django.db.models import Transform +from django.db.models.sql.compiler import Query from django.db.models.lookups import PostgresOperatorLookup from .search import SearchVector, SearchVectorExact, SearchVectorField @@ -18,6 +19,13 @@ class Overlap(PostgresOperatorLookup): lookup_name = "overlap" postgres_operator = "&&" + def process_rhs(self, qn, connection): + if isinstance(self.rhs, Query): + from .expressions import ArraySubquery + self.rhs = ArraySubquery(self.rhs) + rhs, params = super().process_rhs(qn, connection) + return rhs, params + class HasKey(PostgresOperatorLookup): lookup_name = "has_key" diff --git a/tests/postgres_tests/test_array.py b/tests/postgres_tests/test_array.py index c23ed9fe0c..9dd56630f9 100644 --- a/tests/postgres_tests/test_array.py +++ b/tests/postgres_tests/test_array.py @@ -384,6 +384,19 @@ class TestQuerying(PostgreSQLTestCase): [obj_1, obj_2], ) + def test_overlap_values_array_field(self): + # issue 26056 + post1 = CharArrayModel(field=['django']) + post2 = CharArrayModel(field=['thoughts']) + post3 = CharArrayModel(field=['tutorial']) + CharArrayModel.objects.bulk_create([post1, post2, post3]) + qs = CharArrayModel.objects.filter(field__overlap=CharArrayModel.objects.values_list('field', flat=True)) + self.assertSequenceEqual(qs.values_list('field', flat=True), [ + (['django']), + (['thoughts']), + (['tutorial']), + ]) + def test_lookups_autofield_array(self): qs = ( NullableIntegerArrayModel.objects.filter(
comment:11 by , 2 years ago
bcail, yep that seems about right!
Could you submit a PR that does that with a commit message that mentions Mads Jensen and kosz85 efforts and add a mention to the release notes for 4.2?
Once done unchecking patch needs improvement here should get it under reviewers' eyes.
comment:12 by , 2 years ago
Patch needs improvement: | unset |
---|
comment:13 by , 2 years ago
Owner: | set to |
---|---|
Status: | new → assigned |
comment:14 by , 2 years ago
Patch needs improvement: | set |
---|
comment:15 by , 2 years ago
Patch needs improvement: | unset |
---|---|
Triage Stage: | Accepted → Ready for checkin |
comment:17 by , 16 months ago
Hi! I feel late to the battle, because I just upgraded from Django 4.1 to Django 4.2, and I have an issue with this improvement.
I'll try to give a short example.
class Post(Model): name = CharField() tags = ArrayField(CharField()) class User(Model): post = ForeignKey(Post) current_post = Post.objects.filter(pk=OuterRef("post")) matching_posts = Post.objects.filter(Q(tags__overlap=current_post.values("tags")) user = User.objects.annotate(matching_posts=matching_posts.values("name"))
This worked well before because we rely on current_post.values to return a single value.
But now, this current_post.values is wrapped with a call to Array, and I get cannot accumulate empty arrays
error if the post has no tags.
Before:
WHERE tags && (SELECT tags FROM posts WHERE id = 123)
Now:
WHERE tags && ARRAY(SELECT tags FROM posts WHERE id = 123)
Maybe there is another (and better) way to achieve this, but it seems that it has broken my use of .values in the right side.
If this is not clear enough, please tell me and I will setup a more thorough example (I haven't tested this one, it's extracted from my work codebase).
comment:18 by , 16 months ago
Martin, thanks for the report, however it works for me with an empty subquery, e.g.
-
tests/postgres_tests/test_array.py
diff --git a/tests/postgres_tests/test_array.py b/tests/postgres_tests/test_array.py index f7615c974e..9a3966a821 100644
a b class TestQuerying(PostgreSQLTestCase): 423 423 self.objs[:3], 424 424 ) 425 425 426 def test_overlap_values_empty(self): 427 qs = NullableIntegerArrayModel.objects.filter(order__lt=0) 428 self.assertSequenceEqual( 429 NullableIntegerArrayModel.objects.filter( 430 field__overlap=qs.values_list("field"), 431 ), 432 [], 433 ) 434 self.assertSequenceEqual( 435 NullableIntegerArrayModel.objects.filter( 436 field__overlap=NullableIntegerArrayModel.objects.none().values("field"), 437 ), 438 [], 439 ) 440 426 441 def test_lookups_autofield_array(self): 427 442 qs = ( 428 443 NullableIntegerArrayModel.objects.filter(
If you believe it's an issue in Django, then please create a new ticket in Trac and follow our bug reporting guidelines. A sample project that reproduces your issue would be very helpful.
Accepting as a new feature by assuming you meant
B.objects.filter(foo).values('pk')
in your reported example.The implementation could simply use the PostgreSQL
array()
function to wrap the queryset.