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 <[email protected] <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 [email protected] <javascript:>. >> To post to this group, send email to [email protected] >> <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 [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/beabc4fb-b8b5-4d99-a165-c1890cb5e564%40googlegroups.com. For more options, visit https://groups.google.com/d/optout.
