Hmmm, it looks interesting also! I'm just using the postgres version of
your previous solution, and modified / added some features, so pg type
ARRAY could be used with labels and to specify a maximum recursion level
(let's say, do not go more then 10 levels (not relative to start_node,
still)). I'll see after this solution and see which can be best applied :-)
Probably I'll create an Oracle server here to test it too.
Thank you very much,
Richard.
On 04/29/2013 02:57 PM, Mariano Mara wrote:
On 04/25/2013 11:17 AM, Richard Gerd Kuesters wrote:
Well. I'm pretty interested :) I did find your solution very
flexible, thou.
Thanks a lot,
Richard.
For completeness, here is a pure sqlalchemy version of the same
recursive ideas:
def _hierarchy():
# TODO: check if object has self referential key
sq1 = S.query(self.model)
sq1 = sq1.add_columns(*(label("level", literal(1,
type_=Integer)),
label("cycle", literal(False,
type_=Boolean)),
label("connect_path",
literal_column("ARRAY[id]",
ARRAY(Integer)
))))
sq1 = sq1.filter(self.model.parent_id==None)
sq1 = sq1.cte(name="sq1", recursive=True)
sq1_alias = aliased(sq1, name="pr")
sq2_alias = aliased(self.model, name="p")
sq1 = sq1.union_all(
S.query(sq2_alias,
label("level", sq1_alias.c.level+1),
label("cycle", Any(sq2_alias.id,
sq1_alias.c.connect_path)),
label("connect_path",
func.array_append(sq1_alias.c.connect_path,
sq2_alias.id))
).filter(and_(sq2_alias.parent_id==sq1_alias.c.id,
~sq1_alias.c.cycle)))
q = S.query(sq1)
q = q.add_columns(*(label("is_leaf",
case([(
sq1.c.connect_path.contained_by(
over(func.lead(
sq1.c.connect_path,
1),order_by=sq1.c.connect_path))==True,
False)], else_=True)),))
return q.all()
It could use some testing (something I am about to do) and you still
need to cast to your object but all the ingredients are here. As usual
the model needs a self referential key (in this case "id" and
"parent_id"). I try not to use the textual "ARRAY[id]" to generate the
first connect_path but I couldn't make it without errors (and the time
was pressing): If you have a suggestion on how to do so, I will
surely appreciate it.
Mariano
On 04/25/2013 11:08 AM, Mariano Mara wrote:
On 04/25/2013 10:22 AM, Richard Gerd Kuesters wrote:
Hi all,
I've been playing with "sqla_hierarchy" from
https://github.com/marplatense/sqla_hierarchy .
That code of that sqla_hierarchy was written to provide a limited
support for cte, from the time when sqalchemy didn't have cte.
Since sqlalchemy has cte now I would recommend to migrate to pure
sqlalchemy code which will open more possibilities for you to interact
with your objects and provide support in the long term.
I am currently trying to do so (and once I solve how to get *level*,
*is_leaf* and *connect_path* attributes as part of the results, I can
surely share it through the list for anyone who's interested).
Mariano
--
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?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
