[Django] #27149: Filtering with generic relation
Django
----------------------------------------------+----------------------------
Reporter: MikiSoft | Owner: nobody
Type: New feature | Status: new
Component: Database layer (models, ORM) | Version:
Severity: Normal | Keywords: QuerySet.extra
Triage Stage: Unreviewed | Has patch: 0
Easy pickings: 0 | UI/UX: 0
----------------------------------------------+----------------------------
The following function is used for filtering by generic relation (and also
by one column in the model where it is) which isn't natively supported by
Django.
{{{
def generic_rel_filter(model, target, column, id):
return model.objects.extra(where=['''
{app_label}_{model}.id in (select object_id
from {app_label}_{target}
where content_type_id = (select id from django_content_type where
model = '{model}')
and {column} =
{id})'''.format(app_label=os.path.basename(os.path.dirname(__file__)),
model=model.__name__.lower(), target=target, column=column, id=id)])
}}}
''Example:'' If I have Event and Like model, and the second one has
generic relation to the first one (i.e. it has `content_type`, `object_id`
and `content_object` fields), then if I want to get all events which
current user liked, I would just make this call in a view:
`generic_rel_filter(Event, 'like', 'person', self.request.user.pk)`
'''Note that this function isn't intended to be used with user specified
parameters, otherwise it's prone to SQL injection attacks.'''
--
Ticket URL: <https://code.djangoproject.com/ticket/27149>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Django
Reporter: MikiSoft | Owner: nobody
Type: New feature | Status: new
(models, ORM) |
Severity: Normal | Resolution:
Keywords: QuerySet.extra | Triage Stage:
| Unreviewed
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Changes (by MikiSoft):
* needs_better_patch: => 0
* needs_tests: => 0
* needs_docs: => 0
Old description:
> The following function is used for filtering by generic relation (and
> also by one column in the model where it is) which isn't natively
> supported by Django.
>
> {{{
> def generic_rel_filter(model, target, column, id):
> return model.objects.extra(where=['''
> {app_label}_{model}.id in (select object_id
> from {app_label}_{target}
> where content_type_id = (select id from django_content_type where
> model = '{model}')
> and {column} =
> {id})'''.format(app_label=os.path.basename(os.path.dirname(__file__)),
> model=model.__name__.lower(), target=target, column=column, id=id)])
> }}}
>
> ''Example:'' If I have Event and Like model, and the second one has
> generic relation to the first one (i.e. it has `content_type`,
> `object_id` and `content_object` fields), then if I want to get all
> events which current user liked, I would just make this call in a view:
> `generic_rel_filter(Event, 'like', 'person', self.request.user.pk)`
>
> '''Note that this function isn't intended to be used with user specified
> parameters, otherwise it's prone to SQL injection attacks.'''
New description:
The following function is used for filtering by generic relation (and also
by one column in the model where it is) which isn't natively supported by
Django.
{{{
def generic_rel_filter(model, target, column, id):
return model.objects.extra(where=['''
{app_label}_{model}.id in (select object_id
from {app_label}_{target}
where content_type_id = (select id from django_content_type where
model = '{model}')
and {column} =
{id})'''.format(app_label=os.path.basename(os.path.dirname(__file__)),
model=model.__name__.lower(), target=target, column=column, id=id)])
}}}
''Example:'' If I have Event and Like model, and the second one has
generic relation to the first one (i.e. it has `content_type`, `object_id`
and `content_object` fields), then if I want to get all events which
current user liked, I would just make this call in a view:
`generic_rel_filter(Event, 'like', 'person', self.request.user.pk)`
'''Note that this function isn't intended to be used with user specified
parameters, otherwise it's prone to SQL injection attacks.'''
P.S. It can be done with ORM but then it would go with three queries,
which is much slower than the method above (which uses only one query to
do the same):
`Event.objects.filter(pk__in=Like.objects.filter(content_type=ContentType.objects.get(model='event'),
person=self.request.user).values_list('object_id', flat=True))`
--
--
Ticket URL: <https://code.djangoproject.com/ticket/27149#comment:1>
Django
Reporter: MikiSoft | Owner: nobody
Type: New feature | Status: new
(models, ORM) |
Severity: Normal | Resolution:
| Unreviewed
Easy pickings: 0 | UI/UX: 0
Description changed by MikiSoft:
Old description:
> The following function is used for filtering by generic relation (and
> also by one column in the model where it is) which isn't natively
> supported by Django.
>
> {{{
> def generic_rel_filter(model, target, column, id):
> return model.objects.extra(where=['''
> {app_label}_{model}.id in (select object_id
> from {app_label}_{target}
> where content_type_id = (select id from django_content_type where
> model = '{model}')
> and {column} =
> {id})'''.format(app_label=os.path.basename(os.path.dirname(__file__)),
> model=model.__name__.lower(), target=target, column=column, id=id)])
> }}}
>
> ''Example:'' If I have Event and Like model, and the second one has
> generic relation to the first one (i.e. it has `content_type`,
> `object_id` and `content_object` fields), then if I want to get all
> events which current user liked, I would just make this call in a view:
> `generic_rel_filter(Event, 'like', 'person', self.request.user.pk)`
>
> '''Note that this function isn't intended to be used with user specified
> parameters, otherwise it's prone to SQL injection attacks.'''
>
> P.S. It can be done with ORM but then it would go with three queries,
> which is much slower than the method above (which uses only one query to
> do the same):
> `Event.objects.filter(pk__in=Like.objects.filter(content_type=ContentType.objects.get(model='event'),
> person=self.request.user).values_list('object_id', flat=True))`
New description:
The following function is used for filtering by generic relation (and also
by one column in the model where it is) which isn't natively supported by
Django.
{{{
APP_LABEL = os.path.basename(os.path.dirname(__file__))
def generic_rel_filter(model, target, column, id):
return model.objects.extra(where=['''
{app_label}_{model}.id in (select object_id
from {app_label}_{target}
where content_type_id = (select id from django_content_type where
model = '{model}')
and {column} = {id})'''.format(app_label=APP_LABEL,
model=model.__name__.lower(), target=target, column=column, id=id)])
}}}
''Example:'' If I have Event and Like model, and the second one has
generic relation to the first one (i.e. it has `content_type`, `object_id`
and `content_object` fields), then if I want to get all events which
current user liked, I would just make this call in a view:
`generic_rel_filter(Event, 'like', 'person', self.request.user.pk)`
'''Note that this function isn't intended to be used with user specified
parameters, otherwise it's prone to SQL injection attacks.'''
P.S. It can be done with ORM but then it would go with three queries,
which is much slower than the method above (which uses only one query to
do the same):
`Event.objects.filter(pk__in=Like.objects.filter(content_type=ContentType.objects.get(model='event'),
person=self.request.user).values_list('object_id', flat=True))`
--
--
Ticket URL: <https://code.djangoproject.com/ticket/27149#comment:2>
Django
Reporter: MikiSoft | Owner: nobody
Type: New feature | Status: new
(models, ORM) |
Severity: Normal | Resolution:
| Unreviewed
Easy pickings: 0 | UI/UX: 0
Comment (by shaib):
Hi,
Thanks for the suggestion; the code, as given, is not fit for general use
because it makes assumptions which are not guaranteed to hold:
- it assumes the `model` and `target` belong to the same app
- it assumes the modles use the default table names (i.e. none of the
models specifies `Meta.db_table`)
- it assumes the default app label (a different one can be set via
`AppConfig` objects)
- it assumes the names `content_type_id` and `object_id` for the
components of the generic FK -- these are not even defaults, but only a
convention
Further, the code uses `target` in a way which bakes some of these
assumptions into the API, not just the implementation.
I suggest you bring the idea up on the DevelopersMailingList, to get a
wider discussion of whether the feature fits for inclusion in Django, and
if so, to define an API everyone agrees on. When you do, please make sure
you present the problem before you suggest your solution.
--
Ticket URL: <https://code.djangoproject.com/ticket/27149#comment:3>
Django
Reporter: MikiSoft | Owner: nobody
Type: New feature | Status: new
(models, ORM) |
Severity: Normal | Resolution:
| Unreviewed
Easy pickings: 0 | UI/UX: 0
Comment (by timgraham):
I think the intention of this ticket was to present a case where
`QuerySet.extra` is required. If we want to deprecate it, we need to
provide an alternative for accomplishing this.
--
Ticket URL: <https://code.djangoproject.com/ticket/27149#comment:4>
Django
Reporter: MikiSoft | Owner: nobody
Type: New feature | Status: new
(models, ORM) |
Severity: Normal | Resolution:
| Unreviewed
Easy pickings: 0 | UI/UX: 0
Comment (by MikiSoft):
Exactly, timgraham. Thanks for clearing it up instead of me. :)
--
Ticket URL: <https://code.djangoproject.com/ticket/27149#comment:5>
Django
-------------------------------------+-------------------------------------
Reporter: MikiSoft | Owner: nobody
Type: New feature | Status: new
(models, ORM) |
Severity: Normal | Resolution:
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Changes (by timgraham):
* stage: Unreviewed => Accepted
Comment:
A [https://github.com/django/django/pull/6478 PR] provides the ability to
annotate with `SubQuery` and `Exists`. I think this is a bit different
since it applies to `QuerySet.filter()` rather than `annotate()`.
--
Ticket URL: <https://code.djangoproject.com/ticket/27149#comment:6>
Django
-------------------------------------+-------------------------------------
| Schinckel
Type: New feature | Status: assigned
Component: Database layer | Version:
(models, ORM) |
Severity: Normal | Resolution:
Exists |
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 1
Easy pickings: 0 | UI/UX: 0
Changes (by Matthew Schinckel):
* status: new => assigned
* needs_better_patch: 0 => 1
* has_patch: 0 => 1
* owner: nobody => Matthew Schinckel
* keywords: QuerySet.extra => Queryset SubQuery Exists
Comment:
For what it's worth, I have a PR about this (thanks to whoever pointed
this issue out).
However, there is still one outstanding issue related to using
.filter(foo__in=SubQuery(...))
Feel free to jump in with suggestions about how to resolve it at
https://github.com/django/django/pull/6478
--
Ticket URL: <https://code.djangoproject.com/ticket/27149#comment:7>
Django
-------------------------------------+-------------------------------------
Reporter: MikiSoft | Owner: Matthew
| Schinckel
Type: New feature | Status: assigned
Component: Database layer | Version:
(models, ORM) |
Severity: Normal | Resolution:
Exists | checkin
Has patch: 1 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Changes (by Tim Graham):
* needs_better_patch: 1 => 0
* stage: Accepted => Ready for checkin
--
Ticket URL: <https://code.djangoproject.com/ticket/27149#comment:8>
Django
-------------------------------------+-------------------------------------
Reporter: MikiSoft | Owner: Matthew
| Schinckel
Component: Database layer | Version:
(models, ORM) |
Keywords: Queryset SubQuery | Triage Stage: Ready for
Exists | checkin
Has patch: 1 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Changes (by Tim Graham <timograham@…>):
* status: assigned => closed
* resolution: => fixed
Comment:
In [changeset:"236ebe94bfe24d394d5b49f4405da445550e8aa6" 236ebe94]:
{{{
#!CommitTicketReference repository=""
revision="236ebe94bfe24d394d5b49f4405da445550e8aa6"
Fixed #27149 -- Added Subquery and Exists database expressions.
Thanks Josh Smeaton for Oracle fixes.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/27149#comment:9>
Django
-------------------------------------+-------------------------------------
Reporter: MikiSoft | Owner: Matthew
| Schinckel
Type: New feature | Status: closed
Component: Database layer | Version:
(models, ORM) |
Severity: Normal | Resolution: fixed
Keywords: Queryset SubQuery | Triage Stage: Ready for
Exists | checkin
Has patch: 1 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Comment (by Tim Graham <timograham@…>):
In [changeset:"96b6ad94d9ebbd57b77b44e185ee215b5b899ac8" 96b6ad9]:
{{{
#!CommitTicketReference repository=""
revision="96b6ad94d9ebbd57b77b44e185ee215b5b899ac8"
Refs #27149 -- Made Subquery store Query instead of Queryset.
Subquery only uses Query.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/27149#comment:10>
Django
-------------------------------------+-------------------------------------
Reporter: MikiSoft | Owner: Matthew
| Schinckel
Type: New feature | Status: closed
Component: Database layer | Version:
(models, ORM) |
Severity: Normal | Resolution: fixed
Keywords: Queryset SubQuery | Triage Stage: Ready for
Exists | checkin
Has patch: 1 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Comment (by Tim Graham <timograham@…>):
In [changeset:"3a505c70e7b228bf1212c067a8f38271ca86ce09" 3a505c7]:
{{{
#!CommitTicketReference repository=""
revision="3a505c70e7b228bf1212c067a8f38271ca86ce09"
Refs #27149, #29542 -- Simplified subquery parentheses wrapping logic.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/27149#comment:12>
Django
-------------------------------------+-------------------------------------
Reporter: MikiSoft | Owner: Matthew
| Schinckel
Type: New feature | Status: closed
Component: Database layer | Version:
(models, ORM) |
Severity: Normal | Resolution: fixed
Keywords: Queryset SubQuery | Triage Stage: Ready for
Exists | checkin
Has patch: 1 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Comment (by Tim Graham <timograham@…>):
In [changeset:"35431298226165986ad07e91f9d3aca721ff38ec" 35431298]:
{{{
#!CommitTicketReference repository=""
revision="35431298226165986ad07e91f9d3aca721ff38ec"
Refs #27149 -- Moved subquery expression resolving to Query.
This makes Subquery a thin wrapper over Query and makes sure it respects
the Expression source expression API by accepting the same number of
expressions as it returns. Refs #30188.
It also makes OuterRef usable in Query without Subquery wrapping. This
should allow Query's internals to more easily perform subquery push downs
during split_exclude(). Refs #21703.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/27149#comment:11>
Django
-------------------------------------+-------------------------------------
Reporter: MikiSoft | Owner: Matthew
| Schinckel
Type: New feature | Status: closed
Component: Database layer | Version:
(models, ORM) |
Severity: Normal | Resolution: fixed
Keywords: Queryset SubQuery | Triage Stage: Ready for
Exists | checkin
Has patch: 1 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Comment (by Mariusz Felisiak <felisiak.mariusz@…>):
In [changeset:"c0969ee22741f15841a55b4420c113a9eac1c7a5" c0969ee]:
{{{
#!CommitTicketReference repository=""
revision="c0969ee22741f15841a55b4420c113a9eac1c7a5"
Refs #27149 -- Based recursive nested subquery detection on
sys.getrecursionlimit().
This makes sure the test_avoid_infinite_loop_on_too_many_subqueries test
doesn't fail on systems with a non-default recursion limit.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/27149#comment:13>
Django
-------------------------------------+-------------------------------------
Reporter: MikiSoft | Owner: Matthew
| Schinckel
Type: New feature | Status: closed
Component: Database layer | Version:
(models, ORM) |
Severity: Normal | Resolution: fixed
Keywords: Queryset SubQuery | Triage Stage: Ready for
Exists | checkin
Has patch: 1 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Comment (by Mariusz Felisiak <felisiak.mariusz@…>):
In [changeset:"6d0cbe42c3d382e5393d4af48185c546bb0ada1f" 6d0cbe42]:
{{{
#!CommitTicketReference repository=""
revision="6d0cbe42c3d382e5393d4af48185c546bb0ada1f"
Fixed #32650 -- Fixed handling subquery aliasing on queryset combination.
This issue started manifesting itself when nesting a combined subquery
relying on exclude() since 8593e162c9cb63a6c0b06daf045bc1c21eb4d7c1 but
sql.Query.combine never properly handled subqueries outer refs in the
first place, see QuerySetBitwiseOperationTests.test_subquery_aliases()
(refs #27149).
Thanks Raffaele Salmaso for the report.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/27149#comment:15>
Django
-------------------------------------+-------------------------------------
Reporter: MikiSoft | Owner: Matthew
| Schinckel
Type: New feature | Status: closed
Component: Database layer | Version:
(models, ORM) |
Severity: Normal | Resolution: fixed
Keywords: Queryset SubQuery | Triage Stage: Ready for
Exists | checkin
Has patch: 1 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Comment (by Mariusz Felisiak <felisiak.mariusz@…>):
In [changeset:"48e19bae49f271cccbb8a8f4549c9366b7cecac6" 48e19bae]:
{{{
#!CommitTicketReference repository=""
revision="48e19bae49f271cccbb8a8f4549c9366b7cecac6"
[3.2.x] Fixed #32650 -- Fixed handling subquery aliasing on queryset
combination.
This issue started manifesting itself when nesting a combined subquery
relying on exclude() since 8593e162c9cb63a6c0b06daf045bc1c21eb4d7c1 but
sql.Query.combine never properly handled subqueries outer refs in the
first place, see QuerySetBitwiseOperationTests.test_subquery_aliases()
(refs #27149).
Thanks Raffaele Salmaso for the report.
Backport of 6d0cbe42c3d382e5393d4af48185c546bb0ada1f from main
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/27149#comment:16>