IIRC relation lookups don't yet use the custom lookups facility, so just writing a custom lookup might not work. We should turn the related lookups to use the new lookup system, too, but that wasn't done as the way relation lookups work is a bit special inside the ORM.
As for addition of .exists lookup - how should .filter(somefield__exists=False, somefield__othercol=2) work? If both filters must target the same join, then just writing a custom lookup doesn't work. - Anssi On Fri, 2014-08-08 at 00:18 -0700, Josh Smeaton wrote: > 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]> 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. > > 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/1407483680.11410.120.camel%40TTY32. For more options, visit https://groups.google.com/d/optout.
