ArrayField does not work with ValueListQuerySet

[Przemek]

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.

[Simon Charette]

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.

[Mads Jensen]

​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.

[Mads Jensen]

It has been reworked but I suppose other lookups in django.contrib.postgres will want a similar functionality.

[Alex Krupp]

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.

[kosz85]

I posted solution here: ​https://github.com/django/django/pull/7838#issuecomment-337223376
It's Array with Subquery, just pure subquery isn't enough.

[Peter Law]

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',
    ),
)

[Simon Charette]

Now that #32776 landed in the main branch this issue should be straightforward to fix by adjusting the Overlap.process_rhs ​lookup by wrapping a QuerySet instance passed as right-hand-side with ArraySubquery.

[bcail]

@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(

[Simon Charette]

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.

[bcail]

New PR: ​https://github.com/django/django/pull/16303

[Mariusz Felisiak <felisiak.mariusz@…>]

In fbde929:

Fixed #26056 -- Added QuerySet.values()/values_list() support for ArrayField's overlap lookup.

Thanks Mads Jensen and kosz85 and the initial patch.

[Martin Lehoux]

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).

[Mariusz Felisiak]

Martin, thanks for the report, however it works for me with an empty subquery, e.g.

tests/postgres_tests/test_array.py

@@ -423 +423 @@
             self.objs[:3],
         )
 
+    def test_overlap_values_empty(self):
+        qs = NullableIntegerArrayModel.objects.filter(order__lt=0)
+        self.assertSequenceEqual(
+            NullableIntegerArrayModel.objects.filter(
+                field__overlap=qs.values_list("field"),
+            ),
+            [],
+        )
+        self.assertSequenceEqual(
+            NullableIntegerArrayModel.objects.filter(
+                field__overlap=NullableIntegerArrayModel.objects.none().values("field"),
+            ),
+            [],
+        )
+
     def test_lookups_autofield_array(self):
         qs = (
             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.