"and" type query over related objects

[James Cordiner]

Hi,

I need to be able to query an object that has a set of related
objects - the query is to find which objects contain the same set of
query objects. For example the main object could be a research paper
and the related object would be a set of topics that the papers could
contain.

class Topic(models.Model):
label = models.CharField(maxlength=255)

class Paper(models.Model):
topic = models.ManyToManyField(Topic)

I have tried a few approaches, I thought I could simply chain a
series of filters or AND some Q objects:

In [28]: p=Paper.objects.filter(topic=t2).filter(topic=t)

In [29]: p
Out[29]: []

In [39]: Paper.objects.filter(Q(topic=t)|Q(topic=t2))
Out[39]: [<Paper: title1>, <Paper: title2>]

In [40]: Paper.objects.filter(Q(topic=t),Q(topic=t2))
Out[40]: []

But these don't work and I end up with an empty query set, but there
is a Paper object with both a t and t2 object.

Thanks,
jms.

[Tim Chase]

> I need to be able to query an object that has a set of related
> objects - the query is to find which objects contain the same
> set of query objects. For example the main object could be a
> research paper and the related object would be a set of topics
> that the papers could contain.

This is a popular question that I've found a solution to. It
might be kinder in the queryset refactor (Malcolm?), but for the
time being, this is what I've used:

http://groups.google.com/group/django-users/browse_thread/thread/24de9d4b74935296/

Because of the way the underlying SQL builds the query, you
basically end up asking for two contradictory conditions of the
same row:

WHERE x = 1 AND x = 2

which, as you discovered, returns no rows.

The solution I've found is to use an extra() call to build an
IN/EXISTS[1] subselect that asks what I want. Thus, it becomes
something like

WHERE EXISTS(
SELECT 0
FROM <innertbl> i
WHERE <condition1> AND
i.<fk> = <outertbl>.id
) AND EXISTS(
SELECT 0
FROM <innertbl> i
WHERE <condition2> AND
i.<fk> = <outertbl>.id
)

-tim

[1] FWIW, I've found EXISTS to be faster in PostgreSQL and IN to
be faster in SQLServer; haven't profiled MySQL or sqlite.