Filter queryset on "tuples" of values for multiple columns

[Xavier Blanchot]

Hi,

Let's say I have a model Foo with fields a and b, and I would like to build a Foo queryset filtering on some tuples of values for (a,b).
For example, assuming a list of values mylist = [("a1","b1"), ("a2", "b2")]), I want a filter that builds an SQL query with the following WHERE clause:
WHERE (a,b) IN (("a1","b1"), ("a2", "b2")) (*).

I saw a similar question on StackOverFlow​https://stackoverflow.com/questions/20177749/django-filter-queryset-on-tuples-of-values-for-multiple-columns.
The two proposed answers are:

1. Use Q objects which results in a large query with several AND and OR clauses
2. Use raw SQL, which I would rather avoid.

I'd rather have a query with a clause like in (*) so I coded a small filter function which can be used as follows: reusing the previous example, Foo.objects.filter(FieldsIn("a", "b", values=(("a1","b1"), ("a2", "b2")) )). This FieldsIn function inherits from django.db.models.Func and builds the SQL query presented above.

Do you think it's a good idea to integrate such a feature in Django? Otherwise how would you do it?
If my proposition looks good to you I can propose a patch.

[David Sanders]

Hi Xavier,

Thanks for the idea, however feature requests must first be raised on the Django forum in the "Django Internals" category first so that it can reach a wider audience and then be voted on by members of the community. Please see https://code.djangoproject.com/wiki/DevelopersMailingList for more details.

If a consensus is reached then we can reopen this ticket 😉

[Simon Charette]

FWIW ​this was recently discussed on the forums and it feels closely related to #373 so I don't think it warrants further discussion; we know we want to support composite columns fields.

The good news is that ​this is being actively worked on so you should eventually be able to do something like

Foo.objects.filter(
    lookups.CompositeIn(
        expression.ExpressionTuple("a", "b"),  # This would have an output_field=CompositeField()
        tuples,
    )
)

or even eventually

Foo.objects.alias(ab=("a","b")).filter(ab__in=tuples)

If allow tuples to be used as shorthands for expression tuples.

[David Sanders]

There is a way to achieve this, but I've only tested this with PostgreSQL and it only works with psycopg2 – psycopg (3) changes the way tuple adaptation works[1]. (what's more confusing is that if you try this with psycopg (3) it _mogrifies_ fine but if you try to run the query it fails.)

class Foo(Model):
    a = IntegerField()
    b = IntegerField()

Foo.objects.create(a=1, b=1)
Foo.objects.create(a=1, b=2)
Foo.objects.create(a=1, b=3)

qs = (
    Foo.objects.annotate(ab=RawSQL("(a, b)", params=[]))
    .filter(ab__in=[(1, 1), (1, 3)])
    .values("a", "b", "ab")
)
print(qs)

gives me:

<QuerySet [{'a': 1, 'b': 1, 'ab': '(1,1)'}, {'a': 1, 'b': 3, 'ab': '(1,3)'}]>

Disclaimer: This is not an official Django recommendation, merely my own personal testing out of curiosity :D

[1]: ​https://www.psycopg.org/psycopg3/docs/basic/from_pg2.html#you-cannot-use-in-s-with-a-tuple

[Natalia Bidart]

Replying to Simon Charette:

FWIW ​this was recently discussed on the forums and it feels closely related to #373 so I don't think it warrants further discussion; we know we want to support composite columns fields.

Thank you Simon for your clarification, do you think we should mark this as a dupe of #373 then? With the latest information you and David provided, a plain wontfix feels a bit unfair :-)

[Simon Charette]

I think that it's fair to assume this is a dupe of #373 Natalia. The proposed PR ​even have tests for this exact use case.

[Natalia Bidart]

Thanks everyone! Closing as duplicate of #373