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 django-developers+unsubscr...@googlegroups.com. To post to this group, send email to django-developers@googlegroups.com. 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. For more options, visit https://groups.google.com/d/optout.
