query where m2m field is to have values from one set but not another

[James Tauber]

I have a model where each Blah can come from one or more Sources.
i.e. I have:

class Blah(models.Model):
...
sources = models.ManyToManyField(Source)

The query I want to perform is to find all the Blahs that come from
sources 1 or 2 but not sources 3 or 4.

My first attempt was:

Blah.objects.filter(sources__id__in=[1, 2]).exclude(sources__id__in=
[3, 4])

but that just returns all Blahs (the filter(sources__id__in=[1, 2])
is definitely wrong)

The SQL I want to run is something like:

SELECT blah_id
FROM myapp_blah_sources
WHERE source_id IN (1, 2)
EXCEPT
SELECT blah_id
FROM myapp_blah_sources
WHERE source_id in (3, 4);

and this seems to work. Note it's directly referencing the m2m relation.

What's the best way to express that SQL query in the database API?

Note that the lists [1, 2] and [3, 4] are actually coming from a form
and may be empty, in which case the WHERE clause could be eliminated
on that side of the EXCEPT.

Thanks

James

[Pigletto]

> class Blah(models.Model):
> ...
> sources = models.ManyToManyField(Source)
>
> The query I want to perform is to find all the Blahs that come from
> sources 1 or 2 but not sources 3 or 4.

What if Blah comes from both 1 and 3?

> The SQL I want to run is something like:
>
> SELECT blah_id
> FROM myapp_blah_sources
> WHERE source_id IN (1, 2)
> EXCEPT
> SELECT blah_id
> FROM myapp_blah_sources
> WHERE source_id in (3, 4);

Isn't this same as:

SELECT blah_id
FROM myapp_blah_sources
WHERE source_id IN (1, 2)

AND source_id NOT IN (3,4)

Maybe "Complex lookups with Q objects" from
http://www.djangoproject.com/documentation/db-api/
can help here or just use extra like:
Blah.objects.extra(where=['source_id IN (1, 2)', 'source_id NOT IN (3,
4)'])

--
Maciej Wisniowski

[James Tauber]

There's another query I want to do that I can't seem to wrap my head
around at the moment:

Say I have a model where Things have Properties each of a particular
PropertyType. So

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?

James

[r_f_d]

I believe it would be:

Thing.objects.all().exclude(property__property_type__exact =
type).distinct()

-rfd

[Tim Chase]

> 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