#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:
- Use
Q
objects which results in a large query with severalAND
andOR
clauses - 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 , 12 months ago
Resolution: | → wontfix |
---|---|
Status: | new → closed |
follow-up: 4 comment:2 by , 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 , 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
comment:4 by , 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 , 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 , 12 months ago
Resolution: | wontfix → duplicate |
---|
Thanks everyone! Closing as duplicate of #373
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 😉