On Wed, 2008-04-23 at 23:17 -0700, Kevin L wrote:
[...]
> I want to get every parent who does not have a child named "Bob". I
> initially thought this would just be one exclude statement -
>
> Parent.objects.exclude(child__name="Bob")[:100]
>
> However, this gives me a list that includes many parents multiple
> times - basically each parent is in the list once for each child not
> named Bob, even if they do have a child named Bob. This makes sense
> looking at the sql query -
>
> SELECT "foo_parent"."id" FROM "foo_parent" INNER JOIN "foo_child" AS
> "foo_parent__child" ON "foo_parent"."id" =
> "foo_parent__child"."parent_id" WHERE ((NOT
> ("foo_parent__child"."name" = Bob))) DESC LIMIT 100
>
> But what I really want is to discard a parent entirely if they have a
> child named Bob.
>
> Any advice for what I'm doing wrong here, or suggestions on a
> workaround? I'm using django version 0.96. Often > 99% of the parents
> have children named Bob, so I'd rather avoid filtering them out in
> python.
You're not necessarily doing anything wrong here, but this is a case
that 0.96 (and even current Django trunk) doesn't handle. To write a
single SQL query to extract this information requires a nested query.
There's not direct way to do that in 0.96. This is fixed in
queryset-refactor, so will be in trunk shortly, but that doesn't help
you.
One workaround is to do two queries, in effect unrolling the nested SQL
query. Select the id values of the parents with children called "Bob",
then hunt them down and get to them to be more creative in naming their
children! No, wait.. wrong plan. After you've gotten the parent id's you
don't want, you can filter the Parent model to extract the objects you
do want. To wit (untested, but should be close):
unwanted =
Parent.objects.filter(child__name='Bob').distinct().values('id')
id_list = [d['id'] for d in unwanted]
wanted = Parent.objects.exclude(id__in=id_list)
Here, id_list is the list of id values for Parents you don't need. So
you can exclude them in the next query.
Regards,
Malcolm
--
A clear conscience is usually the sign of a bad memory.
http://www.pointy-stick.com/blog/
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Django users" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/django-users?hl=en
-~----------~----~----~----~------~----~------~--~---
