> class Property(models.Model):
>
> thing = models.ForeignKey(Thing)
> property_type = models.ForeignKey(PropertyType)
> value = models.CharField(...)
> ...
>
> I want to find, for a given PropertyType, all the Things that are
> lacking any Property of that type.
>
> Like my previous question, in SQL I could do it with an EXCEPT:
>
> SELECT id
> FROM myapp_thing
> EXCEPT
> SELECT thing_id
> FROM myapp_property
> WHERE property_type_id = ...
>
>
> How can I best do these sorts of EXCEPT queries with the database API?
As Maciej mentioned previously, the easiest way is to use the
.extra() call that the DB API provides:
things = Thing.objects.extra(where=["""
id NOT IN (
SELECT thing_id
FROM myapp_property
WHERE property_type_id = %s
)
"""], params=[42]
)
Both Maciej's SQL and the above SQL should be fairly portable as
long as you're not porting to some older braindead version of
MySQL (pre 4.1, IIRC) that lacks support for subselects [mutters
under breath about that *brilliant* decision: "gee, let's omit a
hugely useful functionality from ANSI SQL..." I'm not sure I'd
even consider pre-5.0 MySQL a "real" database]
Some backends prefer "EXISTS" to "IN" (I noticed that for certain
cases, using EXISTS in PostgreSQL triggered a monumental speed-up
from minutes to seconds) which just takes a minor bit of SQL
tweaking to something like
where=["""
NOT EXISTS (
SELECT 1
FROM myapp_property
WHERE thing_id = myapp_thing.id
AND property_type_id = %s
)
"""]
(adjust the name of "myapp_thing.id" to be whatever that
fieldname is aliased to) YMMV.
-tim
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Django users" group.
To post to this group, send email to django-users@googlegroups.com
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
-~----------~----~----~----~------~----~------~--~---
