我有以下自引用(树)节点，并希望按计算属性 uuid_path 和 name_path 进行过滤/排序:
class Node (db.Model):
id = db.Column (db.Integer, db.Sequence ('node_id_seq'), primary_key=True)
###########################################################################
root_id = db.Column (db.Integer, db.ForeignKey (id, ondelete='CASCADE'),
index=True)
nodes = db.relationship ('Node',
cascade='all, delete-orphan', lazy='dynamic',
primaryjoin='Node.id==Node.root_id',
backref=db.backref ('root', remote_side=id))
###########################################################################
_uuid = db.Column (db.String (36), nullable=False, index=True, unique=True,
name = 'uuid')
_name = db.Column (db.Unicode (256), nullable=False, index=True,
name = 'name')
###########################################################################
@hybrid_property
def uuid (self):
return self._uuid
@hybrid_property
def name (self):
return self._name
@name.setter
def name (self, value):
self._name = value
###########################################################################
def __init__ (self, name, root, mime=None, uuid=None):
self.root = root
self._uuid = uuid if uuid else str (uuid_random ())
self._name = unicode (name) if name is not None else None
def __repr__ (self):
return u'<Node@%x: %s>' % (self.id if self.id else 0, self._name)
###########################################################################
@hybrid_property
def uuid_path (self):
node, path = self, []
while node:
path.insert (0, node.uuid)
node = node.root
return os.path.sep.join (path)
@hybrid_property
def name_path (self):
node, path = self, []
while node:
path.insert (0, node.name)
node = node.root
return os.path.sep.join (path)
###########################################################################
如果我得到一个 Node 实例 subnode 并执行例如subnode.name_path 然后我得到正确的例子根/子节点。但是，如果我尝试使用 Node.name_path(用于过滤/排序)，则 SQLAlchemy 会提示:
Neither 'InstrumentedAttribute' object nor 'Comparator' object associated with Node.root has an attribute 'name'.
我很确定我必须介绍如下内容:
class Node (db.Model):
@hybrid_property
def name_path (self):
node, path = self, []
while node:
path.insert (0, node.name)
node = node.root
return os.path.sep.join (path)
@name_path.expression
def name_path (cls):
## Recursive SQL expression??
但我很难为 @name_path.expression(或 @uuid_path.expression)找到正确的定义；它应该以某种方式指示 SQL 传递从根节点到相关节点的路径。
我不明白为什么这是必需的，因为我已经告诉 SQLAlchemy 迭代计算路径值。感谢您的帮助。
请您参考如下方法:
好吧，在使用 PostgreSQL 和 SQLAlchemy 进行调整后，我想我有一个解决方案:(1) 首先，我将查询编写为 SQL 中的函数，(2) 其次提供正确的 SQLAlchemy 粘合剂:
SQL 部分使用 WITH RECURSIVE CTE:
CREATE OR REPLACE FUNCTION name_path(node)
RETURNS text AS
$BODY$
WITH RECURSIVE graph (id, root_id, id_path, name_path) AS (
SELECT n.id, n.root_id, ARRAY[n.id], ARRAY[n.name]
FROM node n
UNION
SELECT n.id, n.root_id, id_path||ARRAY[n.id], name_path||ARRAY[n.name]
FROM node n, graph g
WHERE n.root_id = g.id)
SELECT array_to_string (g.name_path, '/','.*')
FROM graph g
WHERE (g.id_path[1] = $1.base_id OR g.root_id IS NULL)
AND (g.id = $1.id)
$BODY$
LANGUAGE sql STABLE
COST 100;
ALTER FUNCTION name_path(node)
OWNER TO webed;
SQLAlchemy 端看起来像这样:
class NamePathColumn (ColumnClause):
pass
@compiles (NamePathColumn)
def compile_name_path_column (element, compiler, **kwargs):
return 'node.name_path' ## something missing?
和
class Node (db.Model):
def get_path (self, field):
@cache.version (key=[self.uuid, 'path', field])
def cached_path (self, field):
if self.root:
return self.root.get_path (field) + [getattr (self, field)]
else:
return [getattr (self, field)]
if field == 'uuid':
return cached_path (self, field)
else:
return cached_path.uncached (self, field)
@hybrid_property
def name_path (self):
return os.path.sep.join (self.get_path (field='name'))
@name_path.expression
def name_path (cls):
return NamePathColumn (cls)
如果我在纯 Python 方面，我会避免访问数据库上的 Node.name_path，但如果我愿意的话可能会更快。我仍然不太确定的唯一一件事是在 compile_name_path_column 我没有考虑任何 element, compiler, **kwargs 参数，这让我有点怀疑.
在使用 SA 和 PG 修补了大约 1.5 天之后，我刚刚完成了这个，所以很可能仍有改进的余地。我非常感谢任何评论 w.r.t.这种方法。谢谢。
