[Django] #34940: Filter queryset on "tuples" of values for multiple columns
Django
-------------------------------------+-------------------------------------
Reporter: Blaxav | Owner: nobody
Type: New | Status: new
feature |
Component: Database | Version: 4.2
layer (models, ORM) |
Severity: Normal | Keywords: Filter
Triage Stage: | Has patch: 0
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
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.
--
Ticket URL: <https://code.djangoproject.com/ticket/34940>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Django
-------------------------------------+-------------------------------------
Type: New feature | Status: closed
Component: Database layer | Version: 4.2
(models, ORM) |
Severity: Normal | Resolution: wontfix
Keywords: Filter | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* status: new => closed
* resolution: => wontfix
Comment:
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 😉
--
Ticket URL: <https://code.djangoproject.com/ticket/34940#comment:1>
Django
-------------------------------------+-------------------------------------
Reporter: Xavier Blanchot | Owner: nobody
Type: New feature | Status: closed
Component: Database layer | Version: 4.2
(models, ORM) |
Severity: Normal | Resolution: wontfix
Keywords: Filter | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Simon Charette):
FWIW [https://forum.djangoproject.com/t/multiple-columns-using-mysql-in-
clause/24488 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 [https://github.com/django/django/pull/17279 this is
being actively worked on] so you should eventually be able to do something
like
{{{#!python
Foo.objects.filter(
lookups.CompositeIn(
expression.ExpressionTuple("a", "b"), # This would have an
output_field=CompositeField()
tuples,
)
)
}}}
or even eventually
{{{#!python
Foo.objects.alias(ab=("a","b")).filter(ab__in=tuples)
}}}
If allow tuples to be used as shorthands for expression tuples.
--
Ticket URL: <https://code.djangoproject.com/ticket/34940#comment:2>
Django
-------------------------------------+-------------------------------------
Reporter: Xavier Blanchot | Owner: nobody
Type: New feature | Status: closed
Component: Database layer | Version: 4.2
(models, ORM) |
Severity: Normal | Resolution: wontfix
Keywords: Filter | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by 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
composite type adaptation works. (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 = models.IntegerField()
b = models.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)'}]>
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/34940#comment:3>
Django
-------------------------------------+-------------------------------------
Reporter: Xavier Blanchot | Owner: nobody
Type: New feature | Status: closed
Component: Database layer | Version: 4.2
(models, ORM) |
Severity: Normal | Resolution: wontfix
Keywords: Filter | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Natalia Bidart):
Replying to [comment:2 Simon Charette]:
> FWIW [https://forum.djangoproject.com/t/multiple-columns-using-mysql-in-
clause/24488 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 :-)
--
Ticket URL: <https://code.djangoproject.com/ticket/34940#comment:4>
Django
-------------------------------------+-------------------------------------
Reporter: Xavier Blanchot | Owner: nobody
Type: New feature | Status: closed
Component: Database layer | Version: 4.2
(models, ORM) |
Severity: Normal | Resolution: wontfix
Keywords: Filter | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Simon Charette):
I think that it's fair to assume this is a dupe of #373 Natalia. The
proposed PR [https://github.com/django/django/pull/17279/files#diff-
8c3f17a8ff512a4dff0a0cae1611f191ab3ddb7f5b6aeed7b885d49dd4a831c6R20 even
have tests for this exact use case].
--
Ticket URL: <https://code.djangoproject.com/ticket/34940#comment:5>
Django
-------------------------------------+-------------------------------------
Reporter: Xavier Blanchot | Owner: nobody
Type: New feature | Status: closed
Component: Database layer | Version: 4.2
(models, ORM) |
Keywords: Filter | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* resolution: wontfix => duplicate
Comment:
Thanks everyone! Closing as duplicate of #373
--
Ticket URL: <https://code.djangoproject.com/ticket/34940#comment:6>