Can you create a PR, with docs and tests? If not, we'll need to find someone brave enough to delve into the ORM and add this [quite valid, IMHO] optimisation.
-- C On 8 August 2014 11:16, David Butler <[email protected]> wrote: > > > On Thursday, August 7, 2014 6:48:22 PM UTC-5, Tim Graham wrote: >> >> Does .filter(somefield__isnull=False) not work for what you're trying to >> do? >> > > If I do .filter(somefield__isnull=False) it tries to do a LEFT OUTER JOIN > on the table for somefield instead of a WHERE EXISTS (...) and if that > table is very large then it is pretty slow > > Perhaps the answer is just to modify what __isnull does instead of making > a new lookup > > >> On Thursday, August 7, 2014 7:43:07 PM UTC-4, David Butler wrote: >>> >>> It would be nice if there was some database level optimization for >>> getting objects that have related objects that exist. >>> >>> This is a slow filter: >>> >>> qs = [obj for obj in qs if qs.somefield_set.exists()] >>> >>> Could be faster with something like this: >>> >>> qs = qs.filter(somefield__exists=True) >>> >>> Here is some (rough, probably grossly over simplified but working) code: >>> >>> Code is also available here if it gets garbled: http://dpaste.com/ >>> 0825PNP >>> >>> >>> query_template = ( >>> '{not_part} EXISTS (SELECT 1 FROM "{table1}" WHERE ' >>> '"{table1}"."{table1_column}" = "{table2}"."{table2_column}")' >>> ) >>> def filter_by_reverse_feriegn_key_existance(qs, **kw): >>> >>> for arg, value in kw.items(): >>> >>> assert arg.endswith('__exists') >>> >>> if value is True: >>> not_part = '' >>> elif value is False: >>> not_part = 'NOT' >>> >>> Model = qs.model >>> for field in arg.rstrip('__exists').split('__'): >>> field = Model._meta.get_field_by_name(field)[0] >>> >>> qs = qs.extra(where=[query_template.format( >>> table1=field.model._meta.db_table, >>> table1_column=field.field.db_column, >>> table2=Model._meta.db_table, >>> table2_column=Model._meta.pk.db_column, >>> not_part = not_part >>> )]) >>> >>> Model = field.model >>> return qs >>> >>> >>> This works on postgres, and is ~100x faster >>> >>> I would be interested in any comments >>> >> -- > You received this message because you are subscribed to the Google Groups > "Django developers" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to [email protected]. > To post to this group, send email to [email protected]. > Visit this group at http://groups.google.com/group/django-developers. > To view this discussion on the web visit > https://groups.google.com/d/msgid/django-developers/d4f2d1bc-326d-43d0-a53c-d2b1c25465cd%40googlegroups.com > <https://groups.google.com/d/msgid/django-developers/d4f2d1bc-326d-43d0-a53c-d2b1c25465cd%40googlegroups.com?utm_medium=email&utm_source=footer> > . > > For more options, visit https://groups.google.com/d/optout. > -- You received this message because you are subscribed to the Google Groups "Django developers" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at http://groups.google.com/group/django-developers. To view this discussion on the web visit https://groups.google.com/d/msgid/django-developers/CAG_XiSAaSWExeOSFaf0L-_2Bs3dduM%3D2HDMPninvRH%3DcPm3s4w%40mail.gmail.com. For more options, visit https://groups.google.com/d/optout.
