Using Exclude w/ Many to Many Relationships

[loki77]

I am messing with a server database that I've written in Django, and
I'm running into something that for some reason I can't figure out.

Just some dependency info:
* Using Django from the svn repository- checked out revision 3189.

I have two types of objects (well, more, but the others aren't involved
in this issue). The first are 'Server' objects. The second objects
are 'tag' objects. Servers have a many to many relationship with tags.

What I'm trying to do is get a QueryDict of all the Server objects that
do not have a certain tag, the tag name being 'nopush'.

I tried this:

>>> x = Server.objects.exclude(tags__tag__contains='nopush')
>>> x
[]

But obviously that's not working (there are at least 30 servers in the
database with the 'nopush' tag).

I'm sure there's a simple answer to this, but I'm just not seeing it.
If anyone can point it out to me that'd be awesome. Thanks in advance!

[loki77]

Sorry, I forgot to mention: There are around 300 servers WITHOUT the
nopush tag in the database, and that's the set of servers I would like
to return.

[Malcolm Tredinnick]

Many-to-many relations and filtering are often a little tricky to get
right because of the "many" nature of the join. For me, thinking about
the SQL statement that I want often helps lead to a solution. You want
to select all server_id values from the join table that do not have an
entry for relevant tag_id value. This seems to involve a sub-select, no
matter how you do it (or possibly I just haven't found the right
solution). Usually this means you are going to need to use the extra()
method on the QuerySet, but then you can do it in two queries.

To cut to the chase, this sort of thing should work:

>>> tag_id = Tag.objects.get(tag = 'nopush').id
>>> Server.objects.extra(
... where = ['id not in (select server_id from appname_server_tags where tag_id = %)' % tag_id])

I have guessed slightly at the names for the columns and tables in the
"where" clause here. Modify them to suit your system. If you were really
keen, you could include the tag_id extraction into the
Server.objects.extra() call and have it all done in a single query
(albeit with an extra join).

Regards,
Malcolm

[rajesh...@gmail.com]

I have found that, in some cases, the following also works (I have a
similar model where using the slug of the related object does not work
but using it's PK works):

tag_id = Tag.objects.get(tag = 'nopush').id

x = Server.objects.exclude(tags__id__exact=tag_id)

This may be because using the id saves one join. Normally your original
QuerySet would require a join between three tables: tag, server, and
the tag_server association table. When you use the tag's PK, the
QuerySet does not need to join the tag table.

If you are in DEBUG mode, you can also print out your SQL queries in
your HTML page footer to see how Django is constructing the SQL for
your QuerySet.

[Malcolm Tredinnick]

On Thu, 2006-06-22 at 15:48 +0000, rajesh...@gmail.com wrote:
> I have found that, in some cases, the following also works (I have a
> similar model where using the slug of the related object does not work
> but using it's PK works):
>
> tag_id = Tag.objects.get(tag = 'nopush').id
> x = Server.objects.exclude(tags__id__exact=tag_id)

You said "in some cases", which is important. This won't return the
correct results for an object that has, say, both the "nopush" and
"nopull" tags on it. That object will have two entries in the m2m table
and the entry for the "nopush" tag will be excluded, whereas the other
entry will be selected.

It will work if the tag you are trying to exclude by is always the
*only* tag on a server.

I think you (Rajesh) realise this, since you did qualify your claim. But
I wanted to point it out for when people find this in the archives. I am
truly not intending to demean your observation.

> If you are in DEBUG mode, you can also print out your SQL queries in
> your HTML page footer to see how Django is constructing the SQL for
> your QuerySet.

Particularly for the "why doesn't this work" cases, this is usually a
lifesaver.

Regards,
Malcolm

>
> >
>

[rajesh...@gmail.com]

Malcolm is dead on!

In fact, I should've mentioned that the case that works for me is an
inclusion query rather than an exclusion e.g. find all articles that
have the topic soccer, where articles and topics have a many-to-many
relationship.

For exclusions like the one sought by the OP, an extra SQL clause as
suggested by Malcolm is the way to go.