Excerpts from Michael Bayer's message of Wed May 26 13:23:01 -0300 2010:
>
> 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...
>
For sure it helps, thanks for the pointer. Will try to fix my problem
with it.
Mariano
--
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.
