sqlalchemy 0.7 对象关系备忘录 (2)

自关联表（无级分类或者树结构）

示例，

class Node(Base):
    __tablename__ = 'node'
    id = Column(Integer, primary_key=True)
    parent_id = Column(Integer, ForeignKey('node.id'))  #引用自身id作为外键
    data = Column(String(50))
    children = relationship("Node")   #建立one-to-many关系

结构如图:

root --+---> child1
       +---> child2 --+--> subchild1
       |              +--> subchild2
       +---> child3

字段记录如下:

id       parent_id     data
---      -------       ----
1        NULL          root
2        1             child1
3        1             child2
4        3             subchild1
5        3             subchild2
6        1             child3

relationship()配置在这里建立的是默认的“一对多”的关系，无论你想建立的是“多对一”还是“一对多”都会被假定为“一对多”。为了建立“多对一”的关系，relationship()可以接收额外的指令来完成，那就是“remote_side”。

class Node(Base):
    __tablename__ = 'node'
    id = Column(Integer, primary_key=True)
    parent_id = Column(Integer, ForeignKey('node.id'))
    data = Column(String(50))
    parent = relationship("Node", remote_side=[id])

上面，id被作为了remote_side得参数，成为远程端（多），因此parent_id成为了本地端（一），对象关系标示为“多对一”

添加backref()将关系变为双向。

class Node(Base):
    __tablename__ = 'node'
    id = Column(Integer, primary_key=True)
    parent_id = Column(Integer, ForeignKey('node.id'))
    data = Column(String(50))
    children = relationship("Node",
                backref=backref('parent', remote_side=[id])
            )

自关联表查询

像以前一样的做法：

# get all nodes named 'child2'
session.query(Node).filter(Node.data=='child2')

from sqlalchemy.orm import aliased

nodealias = aliased(Node) #建立别名
SQLsession.query(Node).filter(Node.data=='subchild1').\
                join(nodealias, Node.parent).\
                filter(nodealias.data=="child2").\
                all()

      
      

       
       SELECT node.id AS node_id,
        node.parent_id AS node_parent_id,
        node.data AS node_data
FROM node JOIN node AS node_1
    ON node.parent_id = node_1.id
WHERE node.data = ?
    AND node_1.data = ?
['subchild1', 'child2']
      
      

简化别名：

SQLsession.query(Node).filter(Node.data=='subchild1').\
        join(Node.parent, aliased=True).\  #添加aliased，简化查询写法，但是灵活度减少
        filter(Node.data=='child2').\
        all()

      
      

       
       SELECT node.id AS node_id,
        node.parent_id AS node_parent_id,
        node.data AS node_data
FROM node
    JOIN node AS node_1 ON node_1.id = node.parent_id
WHERE node.data = ? AND node_1.data = ?
['subchild1', 'child2']
      
      

To add criterion to multiple points along a longer join, add from_joinpoint=True to the additional join()calls:

#查询所有以‘root’为根节点，以‘child2’为父节点，名字为‘subchild1’的节点
SQLsession.query(Node).\
        filter(Node.data=='subchild1').\
        join(Node.parent, aliased=True).\
        filter(Node.data=='child2').\
        join(Node.parent, aliased=True, from_joinpoint=True).\
        filter(Node.data=='root').\
        all()

      
      

       
       SELECT node.id AS node_id,
        node.parent_id AS node_parent_id,
        node.data AS node_data
FROM node
    JOIN node AS node_1 ON node_1.id = node.parent_id
    JOIN node AS node_2 ON node_2.id = node_1.parent_id
WHERE node.data = ?
    AND node_1.data = ?
    AND node_2.data = ?
['subchild1', 'child2', 'root']
      
      

Query.reset_joinpoint() will also remove the “aliasing” from filtering calls:

session.query(Node).\
        join(Node.children, aliased=True).\
        filter(Node.data == 'foo').\
        reset_joinpoint().\
        filter(Node.data == 'bar')

For an example of using aliased=True to arbitrarily join along a chain of self-referential nodes, see XML Persistence.

自关联延迟加载

Eager loading of relationships occurs using joins or outerjoins from parent to child table during a normal query operation, such that the parent and its immediate child collection or reference can be populated from a single SQL statement, or a second statement for all immediate child collections. SQLAlchemy’s joined and subquery eager loading use aliased tables in all cases when joining to related items, so are compatible with self-referential joining. However, to use eager loading with a self-referential relationship, SQLAlchemy needs to be told how many levels deep it should join and/or query; otherwise the eager load will not take place at all. This depth setting is configured via join_depth:

class Node(Base):
    __tablename__ = 'node'
    id = Column(Integer, primary_key=True)
    parent_id = Column(Integer, ForeignKey('node.id'))
    data = Column(String(50))
    children = relationship("Node",
                    lazy="joined",
                    join_depth=2)

SQLsession.query(Node).all()

      
      

       
       SELECT node_1.id AS node_1_id,
        node_1.parent_id AS node_1_parent_id,
        node_1.data AS node_1_data,
        node_2.id AS node_2_id,
        node_2.parent_id AS node_2_parent_id,
        node_2.data AS node_2_data,
        node.id AS node_id,
        node.parent_id AS node_parent_id,
        node.data AS node_data
FROM node
    LEFT OUTER JOIN node AS node_2
        ON node.id = node_2.parent_id
    LEFT OUTER JOIN node AS node_1
        ON node_2.id = node_1.parent_id
[]