#34940 closed New feature (duplicate)
Filter queryset on "tuples" of values for multiple columns
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:
- 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 , 16 months ago
Resolution: | → wontfix |
---|---|
Status: | new → closed |
follow-up: 4 comment:2 by , 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 , 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
comment:4 by , 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 , 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 , 16 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 😉