Opened 16 months ago

Closed 16 months ago

Last modified 16 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
Pull Requests:How to create a pull request

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 StackOverFlowhttps://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, 16 months ago

Resolution: wontfix
Status: newclosed

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, 16 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, 16 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 16 months ago by David Sanders (previous) (diff)

in reply to:  2 comment:4 by Natalia Bidart, 16 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, 16 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, 16 months ago

Resolution: wontfixduplicate

Thanks everyone! Closing as duplicate of #373

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