On Aug 10, 2013, at 4:41 PM, Amir Elaguizy aelag...@gmail.com wrote:
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
the IN operator in SQL works like this:
x IN (1, 2, 3, 4, 5, ...)
so if you want to bind values, you have to list them out:
x IN (:value1, :value2, :value3, :value4, ...)
there's no magic acceptance of arrays or anything like that in most SQL drivers.
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()
normally you can make a select() using text fragments, like select(['x', 'y',
'z']).select_from(foo).where(bar 5), though with that CTE and all that
unless you want to write it using the expression language (which I would) it's
easiest to keep that as text(). There's a ticket to make a hybrid text()/FROM
element for this kind of thing (#2478).
for now if it doesn't complain about nesting around that WITH, you can do a
select:
from sqlalchemy import text, select
t1 = text((with recursive ... etc etc ORDER BY depth ASC LIMIT 1) AS
my_query)
s1 = select([id]).select_from(t1).alias()
q = session.query(Entity.x, Entity.y,
...).join(...).filter(...).join(s1, s1.c.id == Entity.id)
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.
signature.asc
Description: Message signed with OpenPGP using GPGMail
