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.
On Mar 29, 4:55 pm, Michael Bayer <mike...@zzzcomputing.com> 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 sqlalchemy@googlegroups.com.
> > To unsubscribe from this group, send email to
> > sqlalchemy+unsubscr...@googlegroups.com.
> > 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 sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
