#27149 closed New feature (fixed)
Allow using a subquery in QuerySet.filter()
Reported by: | MikiSoft | Owned by: | Matthew Schinckel |
---|---|---|---|
Component: | Database layer (models, ORM) | Version: | |
Severity: | Normal | Keywords: | Queryset SubQuery Exists |
Cc: | Triage Stage: | Ready for checkin | |
Has patch: | yes | Needs documentation: | no |
Needs tests: | no | Patch needs improvement: | no |
Easy pickings: | no | UI/UX: | no |
Description (last modified by )
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))
Change History (16)
comment:1 by , 8 years ago
Description: | modified (diff) |
---|
comment:2 by , 8 years ago
Description: | modified (diff) |
---|
comment:3 by , 8 years ago
comment:4 by , 8 years ago
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.
comment:5 by , 8 years ago
Exactly, timgraham. Thanks for clearing it up instead of me. :)
By the way, I've begun to use QuerySet.extra
whenever there are some nested queries (i.e. more than one), and I think that execution is now noticeably faster than earlier.
comment:6 by , 8 years ago
Summary: | Filtering with generic relation → Allow using a subquery in QuerySet.filter() |
---|---|
Triage Stage: | Unreviewed → Accepted |
A 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()
.
comment:7 by , 8 years ago
Has patch: | set |
---|---|
Keywords: | Queryset SubQuery Exists added; QuerySet.extra removed |
Owner: | changed from | to
Patch needs improvement: | set |
Status: | new → assigned |
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
comment:8 by , 8 years ago
Patch needs improvement: | unset |
---|---|
Triage Stage: | Accepted → Ready for checkin |
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:
model
andtarget
belong to the same appMeta.db_table
)AppConfig
objects)content_type_id
andobject_id
for the components of the generic FK -- these are not even defaults, but only a conventionFurther, 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.