On May 26, 2010, at 11:47 AM, Mariano Mara wrote:
> Hi everyone. I have been working in a little class that brings support
> for "with recursive" idiom in my project. Actually the part it supports
> for the moment are the necessary bits to generate hierarchical data (I
> thought somebody might find it useful too so I added it as a recipe in
> the wiki[1]).
>
> The basic idea is that you submit a select expression like (see the wiki
> for the whole example):
>
> select([category.c.id, category.c.name])
>
> and you will get a query like
>
> with recursive rec as (SELECT category.id, category.name, 1 AS level,
> ARRAY[id] AS connect_path
> FROM category
> WHERE coalesce(parent_id, 0) = 0 UNION ALL SELECT category.id,
> category.name, rec.level + 1 AS level, array_append(rec.connect_path,
> category.id) AS connect_path
> FROM category, rec
> WHERE category.parent_id = rec.id) SELECT rec.id, rec.name, rec.level,
> rec.connect_path, case connect_path <@ lead(connect_path, 1) over (order
> by connect_path) when true then false else true end AS is_leaf
> FROM rec order by connect_path
>
> that will give you the same information you requested plus some extra
> columns with hierarchy related info.
>
> The final piece I'm missing is how to pass a where clause: actually I
> cannot make the final sql instruction to accept the parameters I'm
> passing and after hours of trying it seems my sqlalchemy-fu is exhausted
> and I can't fix it by myself.
>
> To illustrate the problem, this is the select with a where clause:
>
> select([category.c.id, category.c.name], category.c.active==True)
I dont have time to read all your source but when you say
category.c.active==True, you get a structure like:
_BinaryExpression
Column('active'), operator.eq, _BindParamClause('active_1', value=True)
the value of that bind maybe could get lost if you aren't compiling the
statement fully with the same compiler object. for example, if you had
something like this:
@compiles(FooBar)
def compile_foo_bar(element, compiler, **kw):
return "FOO BAR " + str(element.value)
the str(element.value), if that is also a ClauseElement, is going to invoke a
whole new compiler with its own set of bind param values. You don't want to
do that. You want to say:
@compiles(FooBar)
def compile_foo_bar(element, compiler, **kw):
return "FOO BAR " + compiler.process(element.value)
thus keeping everything within the same context. this not only maintains all
the binds but also maintains the behavior of the backend being compiled against.
hope this helps...
--
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.
