The query now works as expected, though I'm confused about the SQLAlchemy
code necessary to perform `UNION` on the 2 CTEs.
Changing
...
combined = positive.union(negative)
...
to
...
combined = union(
DBSession.query(positive).subquery('positive'),
DBSession.query(negative).subquery('negative')
).cte('combined')
...
yields this SQL...
WITH positive AS (
SELECT
suitability_match.occupation_id AS occupation_id,
sum(suitability_match.match) AS match
FROM
suitability_match
JOIN user_activity ON
user_activity.activity_id =
suitability_match.activity_id
AND
user_activity.preference = suitability_match.preference
WHERE
user_activity.user_id = %(user_id_1)s
AND
suitability_match.match > %(match_1)s
GROUP BY
suitability_match.occupation_id
),
negative AS (
SELECT
suitability_match.occupation_id AS occupation_id,
sum(suitability_match.match) AS match
FROM
suitability_match
JOIN user_activity ON
user_activity.activity_id =
suitability_match.activity_id
AND
user_activity.preference = suitability_match.preference
WHERE
user_activity.user_id = %(user_id_2)s
AND
suitability_match.match < %(match_2)s
GROUP BY
suitability_match.occupation_id
),
combined AS (
(
SELECT
positive.occupation_id,
positive.match
FROM
positive
) AS positive # This doesn't affect the output but seems
unnecessary
UNION
(
SELECT
negative.occupation_id,
negative.match
FROM
negative
) AS negative # This doesn't affect the output but seems
unnecessary
)
SELECT
occupation_id,
sum(match) AS suitability_match
FROM
combined
GROUP BY
occupation_id;
If instead of
...
combined = union(
DBSession.query(positive).subquery('positive'),
DBSession.query(negative).subquery('negative')
).cte('combined')
...
I write
...
combined = union(
DBSession.query(positive),
DBSession.query(negative)
).cte('combined')
...
the result is an AttributeError
File ".../sqlalchemy/util/_collections.py", line 175, in __getattr__
raise AttributeError(key)
AttributeError: occupation_id
So, why is it necessary to include `.subquery('positive')` and
`.subquery('negative')` when referencing the CTEs in the UNION?
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
