嗨，我正在尝试编写一个 sql 炼金术查询，但它给了我错误"cant determine which from clause to join from"

Question

我有一个sql查询连接表并获取数据

select "FileSets"."Id", "SetFile"."Alias" from "Feeds"
join "FeedSnapshots" on "Feeds"."ActiveSnapshotId"="FeedSnapshots"."Id"
join "Subscriptions" on "Feeds"."Id" = "Subscriptions"."FeedId"
join "SubscriptionSnapshots" on "Subscriptions"."ActiveSnapshotId"="SubscriptionSnapshots"."Id"
join "FileSets" on "SubscriptionSnapshots"."Id"="FileSets"."SubscriptionSnapshotId"
join "SetFile" on "FileSets"."Id"="SetFile"."FileSetId" where "Feeds"."Id"=398 and "Expected"=true

现在我正试图将此转换为sqlAlchemy查询，但它给了我以下错误:

sqlalchemy.exc.InvalidRequestError: Can't determine which FROM clause to join from, there are multiple FROMS which can join to this entity. Please use the .select_from() method to establish an explicit left side, as well as providing an explcit ON clause if not present already to help resolve the ambiguity.

我的sqlAlchemy查询如下所示:

db.session.query(FileSet.id, SetFile.alias).join(FeedSnapshot, Feed.active_snapshot_id == FeedSnapshot.id) 
.join(Subscription, Feed.id == Subscription.feed_id).join(SubscriptionSnapshot, Subscription.active_snapshot_id == SubscriptionSnapshot.id) 
.join(FileSet, SubscriptionSnapshot.id == FileSet.subscription_snapshot_id).join(SetFile, FileSet.id == SetFile.file_set_id) 
.filter(and_(SetFile.expected, Feed.id == orig_feed_snapshot.feed_id)).all()

有人能告诉我我在SqlAlchemy查询中做错了什么吗?

Answer 1

根据您是否愿意，您还可以使用flask_sqlalchemy请求来代替纯sqlalchemy请求。

下面的查询应该可以工作。

id_alias_pairs = Feed.query
.join(FeedSnapshot, Feed.active_snapshot_id == FeedSnapshot.id)
.join(Subscription, Feed.id == Subscription.feed_id)
.join(SubscriptionSnapshot, Subscription.active_snapshot_id == SubscriptionSnapshot.id)
.join(FileSet, SubscriptionSnapshot.id == FileSet.subscription_snapshot_id)
.join(SetFile, FileSet.id == SetFile.file_set_id)
.filter(Feed.id==1, SetFile.expected)
.with_entities(FileSet.id, SetFile.alias)
.all()
print(id_alias_pairs)