On Mar 29, 2011, at 6:50 PM, Corey Coogan wrote: > Thanks MB, that helps me out. I hadn't considered the text portion of > the WHERE. > > The thing is, I'm using WITH RECURSIVE, which gets me a Common Table > Expression (CTE) that represents a folder hierarchy. I want to then > query my entity, apply any filters and make sure my ID appears in the > CTE. Does that make sense? > > Here's the pseudo code of what I want to do and you can tell me if > it's possible. > > sharedFolder = "WITH RECURSIVE q (.....)" > entity = session.query(Entity).filter(Entity.value > > 100).filter(in_(Entity.id,sharedFolder)).first() > > Does that make any sense at all? Basically, I just need to apply the > CTE to my query and filters so I can return the hydrated entity if it > passes the security check done in the WITH RECURSIVE statement.
you can pass text() directly into in_(). But not with other items, I've never heard of "x IN (y, (select z from foo))" syntax before... > > > > > On Mar 29, 4:55 pm, Michael Bayer <[email protected]> wrote: >> On Mar 29, 2011, at 3:33 PM, Corey Coogan wrote: >> >> >> >> >> >> >> >> >> >>> I posted this to Stack Overflow without any luck. Seeing that MB >>> hangs around here, this should be the first place I try. >> >>> I'm using SA 0.6.6, Python 2.66 and Postgres 8.3. >> >>> I have certain queries which require somewhat complex security check >>> that can be handled with a WITH RECURSIVE query. What I'm trying to do >>> is combine a textual query with a query object so I can apply filters >>> as necessary. >> >>> My original thought was was to create my text query as a subquery and >>> then combine that with the user's query and filters. Unfortunately, >>> this isn't working. >> >>> subquery = session.query(sharedFilterAlias).\ >>> from_statement(sharedFilterQuery).subquery() >>> This results in this error: AttributeError: 'Annotated_TextClause' >>> object has no attribute 'alias' >> >>> Is there anyway to combine a textual query with SQLAlchemy's query >>> object? >> >>> I just need to apply the Query and filter to my text query with >>> something like: where Entity.id in (textQueryResult). >> >> Its not clear what SQL you'd be looking for when you say >> from_statement().subquery(). Anything can be in text(), so SQLA can't >> generically select from it - theres no SQL parser so it doesn't know what >> columns would be present inside of it to attach to the .c. collection on an >> Alias object. >> >> Typically its better to not use pure text(), if you're looking to have a .c. >> collection, and to just put your string based stuff in the WHERE clause of a >> select() which is usually where the elaborate stuff is, that is >> select().where("string stuff") or query.filter("string stuff"). select() >> is a little more open ended than Query here in that you can send strings to >> select_from() and others too. >> >> from sqlalchemy import * >> >> s = select(["a", "b"]).select_from("hoho").where("x=5") >> print s >> >> print select([s.c.a, s.c.b]) >> >> SELECT a, b >> FROM hoho >> WHERE x=5 >> SELECT a, b >> FROM (SELECT a, b >> FROM hoho >> WHERE x=5) >> >> >> >> >> >> >> >> >> >>> -- >>> You received this message because you are subscribed to the Google Groups >>> "sqlalchemy" group. >>> To post to this group, send email to [email protected]. >>> To unsubscribe from this group, send email to >>> [email protected]. >>> For more options, visit this group >>> athttp://groups.google.com/group/sqlalchemy?hl=en. > > -- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To post to this group, send email to [email protected]. > To unsubscribe from this group, send email to > [email protected]. > For more options, visit this group at > http://groups.google.com/group/sqlalchemy?hl=en. > -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
