Hey guys,
Questions about the following code in which I'm trying to take a textqual
query and join it to a query builder query.
1) What is the correct way to do the in for the list of ids in the first
query? My current way doesn't work and I'm not able to find a real good
example
2) How can I Join complicated query 2 with complicated query 1. Essentially
join query 2 on sm.StufffModel.id == query1.id
Complicated query 1:
image_res = db.session.query("id", "depth", "parent_id", "name",
"s3_key").from_statement(
"""
WITH RECURSIVE graph(root_id, id, name, parent_id) AS (
SELECT e.id, e.id, e.name, e.parent_id, 1 as depth FROM
entities e
UNION ALL
SELECT graph.root_id, e.id, e.name, e.parent_id, depth + 1 FROM
graph JOIN entities e ON e.parent_id=graph.id
)
SELECT g.id,g.depth, g.parent_id, name, ii.s3_key
FROM graph g
JOIN entity_map em ON g.id=em.left_id
JOIN stufff_images si ON em.right_id=si.id
JOIN image_instance ii ON si.image_id=ii.image_id
WHERE root_id in (:ids) AND ii.width=120 ORDER BY depth ASC LIMIT 1;
""").params(ids=",".join([str(i) for i in ids])))
Complicated query 2:
query = db.session.query(
sm.StufffModel.id, sm.EntityTypesModel.type,
sm.StufffModel.hotness, sm.StufffModel.created_at, sm.StufffModel.name)
query = query.join(sm.EntityTypesModel)
query = query.filter(sm.StufffModel.id.in_(ids))
res = query.all()
Thanks,
Amir
--
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 sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/groups/opt_out.
