SQLAlchemy 中的多个联接经常失败并出现'sqlalchemy.exc.ArgumentError'

Question

我不确定如何正确地使用SQLalchemy进行多个联接。

我试图从get请求中获得一个结果，该请求提供了一个叙述NarrativeModel的列表，其中包含一个或多个引用ReferenceModel，其中publishDate是给定值。在以下示例中，这是2022-03-09

但是，我不知道如何在Narrative、NarrativeReference和Reference之间执行联接。我的尝试大多以以下或类似的方式结束：sqlalchemy.exc.ArgumentError: SQL expression object expected, got object of type <class 'flask_sqlalchemy.model.DefaultMeta'> instead

叙事模式

class NarrativeModel(db.Model):
__tablename__ = "narrative"
id = db.Column(db.Integer, primary_key=True)
title = db.Column(db.String, nullable=False)
date = db.Column(db.String, nullable=True)
ownerProfileID = db.Column(db.Integer, db.ForeignKey('profile.id'), nullable=False)
scheduled = db.Column(db.Integer, nullable=True)
ongoing = db.Column(db.Integer, nullable=True)

叙述性引用模型(联接表(

class NarrativeReferenceModel(db.Model):
__tablename__ = "narrativereference"
id = db.Column(db.Integer, primary_key=True)
narrativeID = db.Column(db.Integer, db.ForeignKey('narrative.id'), nullable=False)
referenceID = db.Column(db.Integer, db.ForeignKey('reference.id'), nullable=False)

引用模型

class ReferenceModel(db.Model):
__tablename__ = "reference"
id = db.Column(db.Integer, primary_key=True)
langCode= db.Column(db.String, nullable=True)
isGov = db.Column(db.String, nullable=True)
type = db.Column(db.String, nullable=True)
title = db.Column(db.String, nullable=True)
domain = db.Column(db.String, nullable=True)
suffix = db.Column(db.String, nullable=True)
description = db.Column(db.String, nullable=True)
publishDate = db.Column(db.String, nullable=True)
addedDate = db.Column(db.String, nullable=True)
url = db.Column(db.String, nullable=False)
youtubeId = db.Column(db.String, nullable=True)
langCheck = db.Column(db.Integer, nullable=True)

获取请求

class GlobalNarrativesByTodaysUpdates (Resource):
#RETURNS ALL AVAILABLE NARRATIVES
# Called with: curl --header "Content-Type: application/json" --request GET http://localhost:8080/narratives
def get(self):
schema = NarrativeSchema()
result = NarrativeModel.query
.join(NarrativeReferenceModel, NarrativeModel.id == NarrativeReferenceModel.narrativeID)
.filter(ReferenceModel, ReferenceModel.publishDate == '2022-03-09').all()

db.session.close()
return schema.dump(result, many=True), 200

一个能满足我需求的SQL语句是：

select distinct narrative.title, narrative.date from narrative, narrativereference
where narrativereference.narrativeID = narrative.id
and narrativereference.referenceID in (select id from reference where publishDate = '2022-03-09')

Answer 1

通过使用简单联接修复了它，因为模型中已经定义了关系

def get(self):
schema = NarrativeSchema()
result = NarrativeModel.query
.join(NarrativeReferenceModel)
.join(ReferenceModel)
.filter(ReferenceModel.addedDate == '2022-03-09').all()
db.session.close()
return schema.dump(result, many=True), 200