great query. CTE’s work surprisingly well with the select_entity_from() method, which typically adds another level of SELECT but with CTE in this case produces the equivalent statement. Note that the type_coerce() on breadcrumbs is because query() wants return values to be hashable when entities are present:
result = session.query(
Division,
hierarchy.c.level,
type_coerce(hierarchy.c.breadcrumbs, ARRAY(Integer,
as_tuple=True)),
).select_entity_from(hierarchy).order_by('breadcrumbs').all()
On Nov 2, 2013, at 9:50 AM, Bérenger Enselme <[email protected]> wrote:
> Hi,
>
> I have an object that models a node in an ordered tree (eg. a table of
> contents), basically with id, parent_id, and order fields. I'm using 0.8.3
>
> I am trying to write a query that would return the whole tree in hierarchical
> order. For this, I'm using a recursive CTE, with a "breadcrumbs" field that
> actually is a postgres array, augmented at each recursion with the node's
> order. The whole query is then sorted on this "breadcrumbs" field.
>
> The code is in this gist:
> https://gist.github.com/benselme/7278872
>
> The problems I'm encountering are:
> * with just the CTE, I get an array of tuples, no mapping to my model
> * I can force the mapping by joining the CTE with the model, and it works (at
> the cost of an extra, useless join), except when I also try to get the
> breadcrumbs field in my result along with the model, in which case I get a
> TypeError: unhashable type: 'list' (see gist for the whole stacktrace)
>
> So the question is, how can I get both the mapped Node models, and the
> breadcrumbs field ? And can I get mapped models driectly from the CTE,
> avoiding the extra join ?
>
> Thanks,
> Bérenger
>
> --
> 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 [email protected].
> To post to this group, send email to [email protected].
> Visit this group at http://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/groups/opt_out.
signature.asc
Description: Message signed with OpenPGP using GPGMail
