CTEs and ORM objects.

[kris]

I need to query over a  table mapped to class T, 

I usually use session.query (T), however I am using two cte's                                                        

part0 =  ... cte('part0')

part1 = ... cte('part1')                                                                                                                        part2 = ... cte('aprt2')                                                                                                                            allparts = union (part1.select(), part2.select())                                                                                  

session.query (allparts)  .. returns row record but I need it to return objects of type T?     

I have tried using   q1 = session.query(T).from_statement (allnodes) 

which appears to convert and create objects of T, However, 

I cannot filter or order_by

I have tried using the sql epxression level:

n = allparts.alias()

 DBSession.query (T).from_statement (select ([n]).where (...).order_by (...)).all()

or 

 DBSession.query(T).from_statement(DBSession.query (n).filter(...).order_by(...).statement).

but I get NoSuchColumnError: "Could not locate column in row for column 'T.id'"

Is there a way to let SA know how map the columns of the select expression to an object?

Thanks.

[Mike Bayer]

I would advise against using from_statement().   the Query object supports cte() and subqueries directly, see the example at https://docs.sqlalchemy.org/en/13/orm/query.html?highlight=query%20cte#sqlalchemy.orm.query.Query.cte

alternatively if you are using 1.4 beta1 then you can use session.execute() directly using a select() object.

--

SQLAlchemy -

The Python SQL Toolkit and Object Relational Mapper

 

http://www.sqlalchemy.org/

 

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description.

---

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+...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/7f27eaff-1f0e-40a9-a225-464414d04041n%40googlegroups.com.

[kris]

Initially I tried to remain solely in ORM constructs, I only broke down and used the SQL expressions after hitting a wall.

 

A little more specifically: 

when I try:

session.query (part1).all ()   

I receive an array of class 'sqlalchemy.util._collections.result'   while I was expecting an array of class T as the part1 cte actually creates a set of T.   

Is there a way to let SA that result of the session.query(T).cte(...) is actually a set of T?

Is  there a way to convert 'result' tuples into class T?

Thanks

[kris]

A little more detail.. 

N = alias (T)

node1 = session.query (N).filter(initial_node_filter).cte ('parents', recursive = True)

parents = topnode.union (DBSession.query(N).filter (N.id == node1.c.parent_id)

print session.query(parents).all()

shows an array 'result'   

How to force it return class N or T

[Mike Bayer]

Please provide a working and minimal mapping of your classes in question as well as the SQL you wish to emit.

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/e71b4af4-ff2f-463c-8007-67165b48fc20n%40googlegroups.com.

[kris]

The example is from adjecency_list.py example.  

The last three lines construct a recursive CTE to walk to the parent from a  found node.

I cannot figure out how to return TreeNodes vs tuples. 

Thanks

================================================

from sqlalchemy import Column

from sqlalchemy import create_engine

from sqlalchemy import ForeignKey

from sqlalchemy import Integer

from sqlalchemy import String

from sqlalchemy.ext.declarative import declarative_base

from sqlalchemy.orm import backref

from sqlalchemy.orm import joinedload_all

from sqlalchemy.orm import relationship

from sqlalchemy.orm import Session

from sqlalchemy.orm.collections import attribute_mapped_collection

Base = declarative_base()

class TreeNode(Base):

    __tablename__ = "tree"

    id = Column(Integer, primary_key=True)

    parent_id = Column(Integer, ForeignKey(id))

    name = Column(String(50), nullable=False)

    children = relationship(

        "TreeNode",

        cascade="all, delete-orphan",

        backref=backref("parent", remote_side=id),

        collection_class=attribute_mapped_collection("name"),

    )

    def __init__(self, name, parent=None):

        self.name = name

        self.parent = parent

    def __repr__(self):

        return "TreeNode(name=%r, id=%r, parent_id=%r)" % (

            self.name,

            self.id,

            self.parent_id,

        )

    def dump(self, _indent=0):

        return (

            "   " * _indent

            + repr(self)

            + "\n"

            + "".join([c.dump(_indent + 1) for c in self.children.values()])

        )

if __name__ == "__main__":

    engine = create_engine("sqlite://", echo=True)

    def msg(msg, *args):

        msg = msg % args

        print("\n\n\n" + "-" * len(msg.split("\n")[0]))

        print(msg)

        print("-" * len(msg.split("\n")[0]))

    msg("Creating Tree Table:")

    Base.metadata.create_all(engine)

    session = Session(engine)

    node = TreeNode("rootnode")

    TreeNode("node1", parent=node)

    TreeNode("node3", parent=node)

    node2 = TreeNode("node2")

    TreeNode("subnode1", parent=node2)

    node.children["node2"] = node2

    TreeNode("subnode2", parent=node.children["node2"])

    msg("Created new tree structure:\n%s", node.dump())

    msg("flush + commit:")

    session.add(node)

    session.commit()

    msg("Tree After Save:\n %s", node.dump())

    TreeNode("node4", parent=node)

    TreeNode("subnode3", parent=node.children["node4"])

    TreeNode("subnode4", parent=node.children["node4"])

    TreeNode("subsubnode1", parent=node.children["node4"].children["subnode3"])

    #  Want to return contents of CTE as TreeNode

    node = session.query(TreeNode).filter(TreeNode.name == "subnode4").cte('parents')

    parents = node.union(session.query(TreeNode).filter(TreeNode.id == node.c.parent_id))

    print session.query(parents).all()

[Mike Bayer]

do you know what SQL string you want?      I can do this quickly if you can send me the exact string.

--

SQLAlchemy -

The Python SQL Toolkit and Object Relational Mapper

 

http://www.sqlalchemy.org/

 

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description.

---

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+...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/f5bdafe1-7c8e-46e9-9771-c8f56fdccbcan%40googlegroups.com.

[kris]

I really appreciate the help.. I've been stuck a while on this.

I am including the SQL string which may help, but to elaborate

I would like to  receive ORM objects that I can further operate on.

  

From above, the line   

        print session.query(parents).all()   

prints 

    [(9, 7, u'subnode4'), (7, 1, u'node4'), (1, None, u'rootnode')]
which are tuples ... 

but I want convert to convert the tuple into TreeNodes. I tried 

   session.query (TreeNode).from_statement (parents.select()).all()

but get 

   sqlalchemy.exc.NoSuchColumnError: "Could not locate column in row for column 'tree.id'"

I actually want to build a more complex express using multiple CTE to walk up and down a tree creating union of following the parent pointer and finding all the children 

of a particular node.   I would then like to filter and order the TreeNodes based on some other criteria.  

It appears I lose track of the type "TreeNode" after the "union" and cannot get it back.  

For completeness here is the fragment I am generating.

WITH RECURSIVE parents AS

   (SELECT tree.id AS id, tree.parent_id AS parent_id, tree.name AS name

   FROM tree

   WHERE tree.name = 'subnode4'

   UNION SELECT tree.id AS tree_id, tree.parent_id AS tree_parent_id, tree.name AS tree_name

   FROM tree, parents

   WHERE tree.id = parents.parent_id),

 kids AS

  (SELECT tree.id AS id, tree.parent_id AS parent_id, tree.name AS name

   FROM tree

   WHERE tree.name = 'subnode4'

   UNION SELECT tree.id AS tree_id, tree.

 AS tree_parent_id, tree.name AS tree_name

   FROM tree, kids

   WHERE tree.parent_id = kids.id)

SELECT parents.id, parents.parent_id, parents.name

FROM parents

UNION

SELECT kids.id, kids.parent_id, kids.name

FROM kids

BUT I want to end up with TreeNode.. Not Tuples.

Thanks again.

[kris]

A more complete version of the SQL to be returned as TreeNode

WITH RECURSIVE

docorder AS ( select id, rn from ...),

parents AS

  (SELECT tree.id AS id, tree.parent_id AS parent_id, tree.name AS name

   FROM tree

   WHERE tree.name = 'subnode4'

   UNION SELECT tree.id AS tree_id, tree.parent_id AS tree_parent_id, tree.name AS tree_name

   FROM tree, parents

   WHERE tree.id = parents.parent_id),

 kids AS

  (SELECT tree.id AS id, tree.parent_id AS parent_id, tree.name AS name

   FROM tree

   WHERE tree.name = 'subnode4'

   UNION SELECT tree.id AS tree_id, tree.parent_id AS tree_parent_id, tree.name AS tree_name

   FROM tree, kids

   WHERE tree.parent_id = kids.id)

SELECT n.id, n.parent_id, n.name

FROM (

   SELECT parents.id, parents.parent_id, parents.name

   FROM parents

   UNION

   SELECT kids.id, kids.parent_id, kids.name

   FROM kids) AS n,

   docorder

WHERE n.id = docorder.id

ORDER BY docorder.rn, n.parent_id

[Mike Bayer]

I would need to see the table defs for "docorder" to do this completely.  

The parents + kids version:

# 1. make parents cte

parents_cte = session.query(TreeNode).filter(TreeNode.name == "subnode4").cte("parents", recursive=True)

p2 = session.query(TreeNode).filter(TreeNode.id == parents_cte.c.parent_id)

parents_cte = parents_cte.union(p2)

# 2. make kids cte

kids_cte = session.query(TreeNode).filter(TreeNode.name == "subnode4").cte("kids", recursive=True)

k2 = session.query(TreeNode).filter(TreeNode.parent_id == kids_cte.c.parent_id)

kids_cte = kids_cte.union(k2)

# 3. SELECT from both CTEs and create a union, then a subquery

from sqlalchemy import union

treenode_rows = union(parents_cte.select(), kids_cte.select()).alias("n")

4. SELECT TreeNode entities from the subuqery (docs: https://docs.sqlalchemy.org/en/13/orm/tutorial.html#selecting-entities-from-subqueries)

from sqlalchemy.orm import aliased

treenode_alias = aliased(TreeNode, treenode_rows)

# 5. select rows

print(session.query(treenode_alias).order_by(treenode_alias.id).all())

this looks like exactly your first query if I'm not mistaken and it returns TreeNode objects per aliased().     hope this gets you started.

--

SQLAlchemy -

The Python SQL Toolkit and Object Relational Mapper

 

http://www.sqlalchemy.org/

 

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description.

---

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+...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/ae4cfacb-ef3f-4d78-961d-45f8ee09f846n%40googlegroups.com.

[kris]

Thank you very much .

aliased (TreeNode, row_expression) was exactly what I was looking for. 

Never noticed that before.