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
