Sqlalchemy的Model和Table需要联表查询问题解决

遇到的问题是，在已经建好的table和modle上怎么做联表，假设表结构如下,ClassA和ClassB是多对多关系

a2b = db.Table(

    'a2b',

    db.Column('a_id', db.Integer(), db.ForeignKey('classa.id')),

    db.Column('b_id', db.Integer(), db.ForeignKey('classb.id'))

)

class ClassA(db.Model):

    __tablename__ = "classa"

    id = db.Column(db.Integer, primary_key=True, autoincrement=True)

    name = db.Column(db.String(64))



class ClassB(db.Model):

    __tablename__ = "classb"

    id = db.Column(db.Integer, primary_key=True, autoincrement=True)

    name = db.Column(db.String(64))

    classas = db.relationship('ClassA', secondary=a2b, backref=db.backref('classb', lazy='dynamic'))

正常是先知道ClassA，然后检索ClassB，现在有个需求是希望同时检索出多个ClassA相关的多个ClassB，然后分页展示，查资料发现table类型可能无法参与查询，解决方案如下

// 已知一堆classa的id

classa_ids = [1,2,3,4]

rets = []

objs = db.session.query(ClassB)

for _id in classa_ids:

    c = db.session.query(ClassA).filter(ClassA.id==_id).first()

    if not c:

        continue

    rets.append(c.classb.with_entities(ClassB.id.label('cid')))

if len(rets) == 1:

    tmp = rets[0].subquery()

elif len(rets) > 1:

    tmp = rets[0].union(*rets[1:]).subquery()



if len(rets) >= 1:

    objs = objs.join(tmp, ClassB.id==tmp.c.cid)

objs.all()