cbmi/harvest-vocab — Issue #2: Support for various multi-item operations
Author: bruth
Created: 2011-11-03 17:34:28+00:00
State: open

Given a list of items to be queried, there are five operations that need to be supported with respect to the related object:

- _at least one of_ - if any one of the values match, this is a hit (the behavior of a SQL `IN` clause)
- _requires all of_ - all must match to be hit
- _not any one of_ - if any one of the values match, this is **not** a hit (negation of SQL `IN` clause)
- _not all of_ - if all of them match, this is **not** a hit
- _only these_ - is a hit only if the object has they matches and only these matches

An example implementation of this is as follows:

```python
class Foo(AbstractItem):
pass

class Bar(models.Model):
foos = models.ManyToManyField(Foo, through='FooBar')

class FooBar(AbstractItemThrough):
foo = models.ForeignKey(Foo)
bar = models.ForeignKey(Bar)
```

SQL for the _requires all of_ operation:

```sql
select id from (select bar_id as id,
sum(case when foo_id = 100 then 1 else 0 end) as "100",
sum(case when foo_id = 200 then 1 else 0 end) as "200"
from foobar group by patient_id) as T
where "100" = 1 and "200" = 1
```

Integrating with the Django ORM:

```python
# sql represents the above SQL statement
subquery = FooBar.objects.raw(sql)

query = Bar.objects.filter(bar__in=subquery)
```

The only caveat as of now is that the `subquery` gets evaluated to a list rather than be injected as a subquery.

All of the operations (except only which is noted below) use the same `CASE` statements in the above SQL. The `WHERE` clause will vary depending on the requirement:

- _at least one of_ - e.g. `"100" = 1 or "200" = 1`
- _requires all of_ - e.g. `"100" = 1 and "200" = 1`
- _not any one of_ - e.g. `not ("100" = 1 or "200" = 1)`
- _not all of_ - e.g. `not ("100" = 1 and "200" = 1)`

To suppor the _only these_ operation, the `CASE` statement booleans need to be swapped like so:

```sql
select id from (select bar_id as id,
sum(case when foo_id = 100 then 0 else 1 end) as "100",
sum(case when foo_id = 200 then 0 else 1 end) as "200"
from foobar group by patient_id) as T
where "100" = 0 and "200" = 0
```

The result should be zero to ensure the queries matches only the ones being queried and not anything else.