Just to be clear, are you (Curtis) suggesting that the isnull lookup should do an EXISTS when looking at a join? I don't think that's a very good idea - having that construct can be extremely useful. I think adding an __exists lookup to relationships would be a great addition - but that's where the PR/docs/tests would come into it. Adding a custom lookup (or building a 3rd party library that introduces that lookup) means you don't need to get too involved with the ORM - you just need to build out the custom Lookup.
On Friday, 8 August 2014 16:48:44 UTC+10, Curtis Maloney wrote: > > 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 <cro...@gmail.com <javascript:>> > 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 django-develop...@googlegroups.com <javascript:>. >> To post to this group, send email to django-d...@googlegroups.com >> <javascript:>. >> 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 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/beabc4fb-b8b5-4d99-a165-c1890cb5e564%40googlegroups.com. For more options, visit https://groups.google.com/d/optout.
