ManyToManyField and Manager.exclude()

[Riccardo Pelizzi]

Hello,

I have a model which looks kinda like this:

class Host(model):
ip = IPAddressField()

class Domain(model):
hosts = ManyToManyField(Host)

If i want to get all the domains without hosts:
Domain.objects.filter(hosts=None)
works.

Later on in my script i need to get the opposite, all the domains
having one or more hosts associated. using exclude instead of filter
with the same condition doesn't work as expected (at least as I
expected :-P), it just returns an empty queryset. Is there a way to
formulate the query using the django api? or should i use custom sql?

thanks
Riccardo

[Malcolm Tredinnick]

That should work properly. It works for me using exactly your models
with a few different Host/Domain combinations.

The SQL we currently generate for Domain.objects.exclude(hosts=None) is
a little inefficient, but it's not fundamentally incorrect. It just uses
a couple more tables than it needs to be because the "NULL" case can
take advantage of one particular extra optimisation that isn't in the
code yet. I'm surprised it isn't returning the correct result.

Another to get the same answer, which generates simpler SQL but is
potentially a little less efficient on the database side is

Domain.objects.filter(hosts__isnull=False).distinct()

That's essentially the only case where Django has a "not" filter
available: you can actually filter for "not NULL". The distinct() call
there is important, as for many-to-many fields, you would otherwise get
back one domain object for every host (so repetitions for domains with
multiple hosts), not just the set of domains that have at least one
host.

In any case, I suspect there's something else going on in your
particular case, as the SQL generated should be pulling back the results
you're after and it works with a small example using your models. If you
can use those models, as pasted, to generate an incorrect result, what
is the smallest amount of data (Host & Domain objects) you need to
create to demonstrate the problem. That would be interesting to see.

Regards,
Malcolm

[Riccardo Pelizzi]

Sorry for the delay. hosts__isnull works!
But i can confirm exclude doesn't:

h = Host.objects.create(ip="0.0.0.0")
d = RegisteredDomain.objects.all()[0]
d.hosts.add(h)
RegisteredDomain.objects.count()
--> 477L (total)
RegisteredDomain.objects.filter(hosts=None).count()
--> 476L (good!)
RegisteredDomain.objects.exclude(hosts=None).count()
--> 0 (not so good :-/)
RegisteredDomain.objects.filter(hosts__isnull=False).count()
--> 1L (good!)

I will paste you both models to be sure:

class Host(TimeStampedModel):
ip = models.IPAddressField(unique=True)

class RegisteredDomain(TimeStampedModel):
domain = models.CharField(max_length=255, unique=True)
spam_time = models.DateTimeField(null=True)
hosts = models.ManyToManyField(Host,
related_name="registered_domains")
random = models.BooleanField(null=True)

Is this a bug or i just didn't get how models work? :-)

Riccardo

On 4 Mai, 21:22, Malcolm Tredinnick <malc...@pointy-stick.com> wrote:
> On Mon, 2009-05-04 at 09:18 -0700, Riccardo Pelizzi wrote:
> > Hello,
>
> > I have a model which looks kinda like this:
>
> > class Host(model):
> >   ip = IPAddressField()
>
> > class Domain(model):
> >   hosts = ManyToManyField(Host)
>
> > If i want to get all the domains without hosts:
> > Domain.objects.filter(hosts=None)
> > works.
>
> > Later on in my script i need to get the opposite, all the domains
> > having one or more hosts associated. using exclude instead of filter
> > with the same condition doesn't work as expected (at least as I
> > expected :-P), it just returns an empty queryset. Is there a way to

> > formulate thequeryusing the django api? or should i use custom sql?

[Riccardo Pelizzi]

duh, i forgot to say that i had 477 registered domain in the database
and 0 hosts before i run this code :-)