About reverse queries in FK relationships
Kilian CAVALOTTI
I have a question about reverse queries in ForeignKey relationships. Taking
the example given in
<http://www.djangoproject.com/documentation/models/many_to_one/>, I know that
I can filter the Reporter objects to get those who have associated Articles,
based on Article properties:
Reporter.objects.filter(article__headline__startswith='This')
I guess I can get all Reporters who have associated Articles by doing:
Reporter.objects.filter(article__isnull = False)
But, is there a way to get Reporters who have *no* associated Article? I tried
Reporter.objects.filter(article__isnull = True)
but it returns an empty list, since the article property does not exist for
those Reporters, and thus, it can't even be null.
The aim is to use this in a limit_choices_to option, to only allow selection
of 'free' Reporters in the admin interface (ie. Reporters who still do not
have written any article, they lazy guys).
TIA,
--
Kilian CAVALOTTI Administrateur réseaux et systèmes
UPMC / CNRS - LIP6 (C870)
8, rue du Capitaine Scott Tel. : 01 44 27 88 54
75015 Paris - France Fax. : 01 44 27 70 00
Malcolm Tredinnick
> Hi all,
>
> I have a question about reverse queries in ForeignKey relationships. Taking
> the example given in
> <http://www.djangoproject.com/documentation/models/many_to_one/>, I know that
> I can filter the Reporter objects to get those who have associated Articles,
> based on Article properties:
>
> Reporter.objects.filter(article__headline__startswith='This')
>
> I guess I can get all Reporters who have associated Articles by doing:
>
> Reporter.objects.filter(article__isnull = False)
>
>
> But, is there a way to get Reporters who have *no* associated Article? I tried
>
> Reporter.objects.filter(article__isnull = True)
>
> but it returns an empty list, since the article property does not exist for
> those Reporters, and thus, it can't even be null.
>
> The aim is to use this in a limit_choices_to option, to only allow selection
> of 'free' Reporters in the admin interface (ie. Reporters who still do not
> have written any article, they lazy guys).
This is a very interesting question. The answer is kind of hinted at in
the documentation (it's the reverse of creating an empty relation set),
but I only realised that after messing around at the interactive prompt
a little to work out the answer.
Reporter.objects.filter(article = None)
will give you the list you want. I'll add something to the documentation
to make this more explicit once I work out how to phrase it.
Best wishes,
Malcolm
Malcolm Tredinnick
[...]
> Reporter.objects.filter(article = None)
>
> will give you the list you want. I'll add something to the documentation
> to make this more explicit once I work out how to phrase it.
Ignore this; it is rubbish! Not even close to correct.
I tested on a ForeignKey example I had lying around, rather than the
exact example you posted. It does not work at all.
So I'm back to trying to work it out. Just wanted to post something
quickly in case you were trying to follow my ill-considered advice.
Regards,
Malcolm
Malcolm Tredinnick
>
> I have a question about reverse queries in ForeignKey relationships. Taking
> the example given in
> <http://www.djangoproject.com/documentation/models/many_to_one/>, I know that
> I can filter the Reporter objects to get those who have associated Articles,
> based on Article properties:
>
> Reporter.objects.filter(article__headline__startswith='This')
>
> I guess I can get all Reporters who have associated Articles by doing:
>
> Reporter.objects.filter(article__isnull = False)
>
>
> But, is there a way to get Reporters who have *no* associated Article? I tried
>
> Reporter.objects.filter(article__isnull = True)
>
> but it returns an empty list, since the article property does not exist for
> those Reporters, and thus, it can't even be null.
>
> The aim is to use this in a limit_choices_to option, to only allow selection
> of 'free' Reporters in the admin interface (ie. Reporters who still do not
> have written any article, they lazy guys).
Let's try this again... take two... (it's still an interesting
question)..
Looking at the SQL queries we generate, I cannot see a way to do what
you want without writing a fragment of custom SQL. It almost feels like
this should be easier, although I am normally fairly conservative about
adding new functionality. Something to think about, certainly.
Anyway, to achieve what you want to do, this will work:
Reporter.objects.extra(where = ['id not in (select reporter_id from app4_article)'])
In this case, "app4" was the name of my application and, if you can't
guess it, you can look up the name of the article table -- app4_article
-- in the table. It is possible to work out the table name
automatically, too, if you really care.
Sorry again about the bogus answer earlier. I am going to try and be
more careful in future. That was just dumb.
Regards,
Malcolm
Kilian CAVALOTTI
> Let's try this again... take two... (it's still an interesting
> question)..
I'm glad it took you some time to answer, I always feel reassuring to see that
more skilled people scratch their head on my problems too. I feel less
dumb. :))
> Looking at the SQL queries we generate, I cannot see a way to do what
> you want without writing a fragment of custom SQL. It almost feels like
> this should be easier, although I am normally fairly conservative about
> adding new functionality. Something to think about, certainly.
>
> Anyway, to achieve what you want to do, this will work:
>
> Reporter.objects.extra(where = ['id not in (select reporter_id from
> app4_article)'])
Hey, that works great, indeed, thank you.
By the way, is there a mean to use this extra() request in a limit_choices_to
field option? I can't figure how to use custom SQL in a Q object.
That said, from my point of view, it would certainly be very convenient to
have a way to get those records without extra SQL. Something like a isdefined
lookup type (ala isnull).
>
> Sorry again about the bogus answer earlier. I am going to try and be
> more careful in future. That was just dumb.
Don't mind, really. I already sincerely appreciate you took time to look after
this. Thanks a lot.
Kudos!