Preventing JOIN while checking if a self referencing FK is null

[John Boxall]

Hey Djangonauts,

I'd like to optimize the performance of a query I've got running on a
page - I've got a model which has a self referencing nullable foreign
key and I'd like to select all instances of that model that which have
a value for that column.

Imagine a class Person with a nullable FK to Person "Best Friends
Forever" - I'd like to select everyone who has a BFF! This is a
straight forward query operation and I would expect the ORM to avoid
using a JOIN to find it out - it would seem you could just check
whether the bff_id isnull - but in all the queries I've tried, the ORM
wants to do a JOIN on Person, which is expensive and unnecessary.

So my question is - how do you prevent a JOIN while checking if a self
referencing FK is null?

# Begin example codes #

class Person(model.Models):
bbf = models.ForeignKey("self", null=True)

...

# Returns a queryset which causes a join
people_with_bffs = Person.objects.filter(bbf__isnull=False)

# Returns a queryset which causes a join
people_with_bffs = Person.objects.filter(bbf__pk__isnull=False)

# Returns a queryset which doesn't cause a join?
people_with_bffs = Person.objects.filter( ??? )



Cheers,

John

[Javier Guerra]

what about

people_with_bffs = Person.objects.filter(bbf_id__isnull=False)

?

(disclaimer, i haven't tried it)

--
Javier

[John Boxall]

Both of these will raise a FieldError:

people_with_bffs = Person.objects.filter(bbf_id__isnull=False)
people_with_bffs = Person.objects.filter(bbf_pk__isnull=False)

Cannot resolve keyword 'bbf_pk' into field. Choices are 'bbf'.

Thanks for the suggestion though!

jb

[Daniel Roseman]

On Nov 4, 7:22 pm, John Boxall <bisha...@gmail.com> wrote:
> Both of these will raise a FieldError:
>
> people_with_bffs = Person.objects.filter(bbf_id__isnull=False)
> people_with_bffs = Person.objects.filter(bbf_pk__isnull=False)
>
> Cannot resolve keyword 'bbf_pk' into field. Choices are 'bbf'.
>
> Thanks for the suggestion though!
>
> jb
>

Try Person.objects.exclude(bbf_id=None)
--
DR.

[John Boxall]

Thanks for the suggestion Daniel, but that doesn't help either :\

This will raise a field error (bff_id is not a valid field, only bff
is)
Person.objects.exclude(bff_id=None)

And these both result in JOINs:
Person.objects.exclude(bff=None)
Person.objects.exclude(bff__id=None)

Perhaps there is an optimization to be made at the Django ORM level?

jb