python - 如何让 sqlalchemy 在 from 之后加入

Question

我的模型：

class ParentModel(db.Model):
    __tablename__ = "parents"
    id = db.Column(db.String(255), primary_key=True)


class Child1Model(db.Model):
    __tablename__ = "children1"

    id = db.Column(db.Integer(), primary_key=True)
    parent_id = db.Column(db.String(255), db.ForeignKey("parents.id", ondelete="RESTRICT"))


class Child2Model(db.Model):
    __tablename__ = "children2"

    id = db.Column(db.Integer(), primary_key=True)
    parent_id = db.Column(db.String(255), db.ForeignKey("parents.id", ondelete="RESTRICT"))

和这个声明：

p1 = aliased(ParentModel)
p2 = aliased(ParentModel)
query = (
    psql_db.session.query(ParentModel, Child1Model, Child2Model)
    .join(p1, Child1Model.parent_id == p1.id)
    .join(p2, Child2Model.parent_id == p2.id)
)

产生以下查询：

SELECT parents.id          AS parents_id,
       children1.id        AS children1_id,
       children1.parent_id AS children1_parent_id,
       children2.id        AS children2_id,
       children2.parent_id AS children2_parent_id
FROM parents,
     children2,
     children1
         JOIN parents AS parents_1 ON children1.parent_id = parents_1.id
         JOIN parents AS parents_2 ON children2.parent_id = parents_2.id

并执行它给出：

invalid reference to FROM-clause entry for table "children2" Hint: There is an entry for table "children2", but it cannot be referenced from this part of the query. Position: 388

我知道它应该看起来更像这样：

SELECT parents.id          AS parents_id,
       children1.id        AS children1_id,
       children1.parent_id AS children1_parent_id,
       children2.id        AS children2_id,
       children2.parent_id AS children2_parent_id
FROM parents,
     children2
         JOIN parents AS parents_2
              ON children2.parent_id = parents_2.id,
     children1
         JOIN parents AS parents_1 ON children1.parent_id = parents_1.id

所以只是在指定表 FROM 之后加入，但我不知道如何在 sqlalchemy 中完成它，有什么想法吗？

标签： pythonsqlsqlalchemy