kvesteri/sqlalchemy-continuum — pull request #83 by dtheodor (2014-11-12 02:48:30+00:00)
Title: Fix for many-to-many query

The original many-to-many query was broken when newer versions of a subset of the relatives are saved. Then the relatives that were not part of the latest transaction are not fetched. Also, associations with 2-column foreign keys were not working.
```sql
SELECT tag_version.*
FROM tag_version
WHERE tag_version.id IN (
SELECT tag_id
FROM article_tag_version
WHERE transaction_id IN (
SELECT max(transaction_id) AS max_1
FROM article_tag_version
WHERE transaction_id <= 5
AND 1 = article_id
GROUP BY tag_id
)
AND 1 = article_id
AND operation_type != 2
)
AND transaction_id = (
SELECT max(transaction_id) AS max_2
FROM tag_version
WHERE transaction_id <= 5
)

```

This patch modifies the query with EXISTS etc. which results into the following terrific query
```sql
SELECT tag_version.id AS tag_version_id
,tag_version.NAME AS tag_version_name
,tag_version.transaction_id AS tag_version_transaction_id
,tag_version.operation_type AS tag_version_operation_type
FROM tag_version
WHERE (
EXISTS (
SELECT 1
FROM article_tag_version
WHERE 1 = article_tag_version.article_id
AND (
EXISTS (
SELECT 1
FROM article_tag_version AS article_tag_version_1
WHERE article_tag_version_1.transaction_id <= 5
AND article_tag_version_1.tag_id = article_tag_version.tag_id
GROUP BY article_tag_version_1.tag_id
HAVING max(article_tag_version_1.transaction_id) = article_tag_version.transaction_id
)
)
AND article_tag_version.operation_type != 2
AND tag_version.id = article_tag_version.tag_id
)
)
AND (
EXISTS (
SELECT 1
FROM tag_version AS tag_version_1
WHERE tag_version_1.transaction_id <= 5
AND tag_version_1.id = tag_version.id
GROUP BY tag_version_1.id
HAVING max(tag_version_1.transaction_id) = tag_version.transaction_id
)
)
AND tag_version.operation_type != 2
```