Hi,
Using SQLAlchemy 0.9.8 and Postgres 9.3.
I'm in the process of re-writing my raw SQL using SQLAlchemy ORM but having
some trouble with my CTEs.
## Raw SQL
WITH suitability_positive AS (
SELECT
occupation_id,
sum(match) AS match
FROM
suitability_match
INNER JOIN user_activity ON
user_activity.activity_id =
suitability_match.activity_id
AND
user_activity.preference = suitability_match.preference
WHERE
user_id = :user_id
AND
match > 0
GROUP BY
occupation_id
),
suitability_negative AS (
SELECT
occupation_id,
sum(match) AS match
FROM
suitability_match
INNER JOIN user_activity ON
user_activity.activity_id =
suitability_match.activity_id
AND
user_activity.preference = suitability_match.preference
WHERE
user_id = :user_id
AND
match < 0
GROUP BY
occupation_id
),
combined AS (
SELECT * FROM positive
UNION
SELECT * FROM negative
)
SELECT
occupation_id,
sum(match) AS suitability_match
FROM
combined
GROUP BY
occupation_id;
So this might not be very optimized, but I have some related queries that
are much larger and using CTEs is the only way I can stay sane. When
re-writing using the ORM, I'm not sure which subqueries I need to be
aliasing and than how to use `UNION` with them. This was my attempt:
## SQLAlchemy
positive = DBSession.query(
SuitabilityMatch.occupation_id,
func.sum(SuitabilityMatch.match).label('match')
)\
.join(UserActivity,
and_(
UserActivity.activity_id == SuitabilityMatch.activity_id,
UserActivity.preference == SuitabilityMatch.preference
)
)\
.filter(
UserActivity.user_id == self.id,
SuitabilityMatch.match > 0
)\
.group_by(
SuitabilityMatch.occupation_id
)\
.cte('positive')
negative = DBSession.query(
SuitabilityMatch.occupation_id,
func.sum(SuitabilityMatch.match).label('match')
)\
.join(UserActivity,
and_(
UserActivity.activity_id == SuitabilityMatch.activity_id,
UserActivity.preference == SuitabilityMatch.preference
)
)\
.filter(
UserActivity.user_id == self.id,
SuitabilityMatch.match < 0
)\
.group_by(
SuitabilityMatch.occupation_id
)\
.cte('negative')
combined = positive.union(negative).cte('combined')
query = DBSession.query(
combined.occupation_id,
func.sum(combined.match).label('suitability_match')
)\
.group_by(
combined.occupation_id
)\
.all()
The `combined` expression yields AttributeError: 'CTE' object has no
attribute 'cte'
So if I change
combined = positive.union(negative).cte('combined')
to
combined = positive.union(negative)
and
query = DBSession.query(
combined.occupation_id,
func.sum(combined.match).label('suitability_match')
)\
.group_by(
combined.occupation_id
)\
.all()
to
query = DBSession.query(
combined*.c*.occupation_id,
func.sum(combined*.c*.match).label('suitability_match')
)\
.group_by(
combined*.c*.occupation_id
)\
.all()
I get SQL, but it's not the SQL I desire. This is the output (indented to
make it more readable):
WITH 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
),
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
)
UNION negative
SELECT
positive.occupation_id AS positive_occupation_id,
sum(positive.match) AS suitability_match
FROM
positive
GROUP BY
positive.occupation_id
Everything looks identical to the original, raw SQL except the UNION and
final SELECT statement, where it looks like `combined` is still referencing
`positive`. I've tried different combinations of `.alias()` and
`.subquery()` but I'm really just guessing at this point.
So...
How do I `UNION` the first 2 queries?
How do I make the result of that `UNION` another CTE?
How do I reference the combined CTE in the final `SELECT` statement?
Thanks!
--
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.
