您好，我正在尝试编写一个sql alchemy查询，但它给了我一个错误“无法确定从哪个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)