Opened 12 months ago

Closed 12 months ago

Last modified 12 months ago

#34940 closed New feature (duplicate)

Filter queryset on "tuples" of values for multiple columns

Reported by: Xavier Blanchot Owned by: nobody
Component: Database layer (models, ORM) Version: 4.2
Severity: Normal Keywords: Filter
Cc: Triage Stage: Unreviewed
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

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.

Change History (6)

comment:1 by David Sanders, 12 months ago

Resolution: → wontfix
Status: new → closed

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 😉

comment:2 by Simon Charette, 12 months ago

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.

comment:3 by David Sanders, 12 months ago

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

Last edited 12 months ago by David Sanders (previous) (diff)

in reply to:  2 comment:4 by Natalia Bidart, 12 months ago

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

comment:5 by Simon Charette, 12 months ago

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.

comment:6 by Natalia Bidart, 12 months ago

Resolution: wontfix → duplicate

Thanks everyone! Closing as duplicate of #373

Note: See TracTickets for help on using tickets.
Back to Top