使用 SQLAlchemy 连接多个表

Question

我想我已经很接近了，但也许我需要一些指导。我正在尝试在 SQL Alchemy 中运行下面的 SQL 查询。

Endpoints for populating the UI with data. These need to consist of some joins.

select
    constraintid,
    sja.segment,
    sjs.source,
    sjct.constrainttype,
    constraintvalue,
    targeting,
    frequency,
    period
from signaljourneyaudienceconstraints
JOIN signaljourneyaudiences sja ON sja.audienceid = signaljourneyaudienceconstraints.audienceid;
JOIN signaljourneysources sjs ON sjs.sourceid = signaljourneyaudienceconstraints.sourceid
JOIN signaljourneyconstrainttype sjct ON sjct.constrainttypeid = signaljourneyaudienceconstraints.constrainttypeid

我读过一些文档，我认为我的思路是正确的，但我不断收到这样的错误

sqlalchemy.exc.ArgumentError: Join target SignalJourneyAudiences.segment does not refer to a mapped entity

，因为 SignalJourneyAudiences.segment 不是关键，我试图复制我知道的 SQL 查询作品。

@app.get("/get-main-query-data", status_code=status.HTTP_200_OK)
def get_main_query_data():
    """Returns data for the main query."""
    return (
        db.query(
            models.SignalJourneyAudienceConstraints.constraintId,
            models.SignalJourneyAudiences.segment,
            models.SingalJourneySources.source,
            models.SignalJourneyConstraintType.constraintType,
            models.SignalJourneyAudienceConstraints.constraintValue,
            models.SignalJourneyAudienceConstraints.targeting,
            models.SignalJourneyAudienceConstraints.frequency,
            models.SignalJourneyAudienceConstraints.period,
        )
        .join(
            models.SignalJourneyAudiences.segment,
            models.SignalJourneyAudiences.audienceId
            == models.SignalJourneyAudienceConstraints.audienceId,
        )
        .join(
            models.SingalJourneySources.source,
            models.SingalJourneySources.sourceId
            == models.SignalJourneyAudienceConstraints.sourceId,
        )
        .join(
            models.SignalJourneyConstraintType.constraintType,
            models.SignalJourneyConstraintType.constraintTypeId
            == models.SignalJourneyAudienceConstraints.constraintTypeId,
        )
        .all()
    )

我是否正确地认为 db.query() 是我的选择子句，然后我执行诸如连接、过滤等操作？

任何帮助将不胜感激

Answer 1

我已经解决了这个问题，我知道我走在正确的轨道上。

我正在使用

.join(
            models.SignalJourneyAudiences.segment,
            models.SignalJourneyAudiences.audienceId
            == models.SignalJourneyAudienceConstraints.audienceId,
        )

而不是

.join(
            models.SignalJourneyAudiences,
            models.SignalJourneyAudiences.audienceId
            == models.SignalJourneyAudienceConstraints.audienceId,
        )

@app.get("/get-main-query-data", status_code=status.HTTP_200_OK)
def get_main_query_data():
    """Returns data for the main query."""
    return (
        db.query(
            models.SignalJourneyAudienceConstraints.constraintId,
            models.SignalJourneyAudiences.segment,
            models.SingalJourneySources.source,
            models.SignalJourneyConstraintType.constraintType,
            models.SignalJourneyAudienceConstraints.constraintValue,
            models.SignalJourneyAudienceConstraints.targeting,
            models.SignalJourneyAudienceConstraints.frequency,
            models.SignalJourneyAudienceConstraints.period,
        )
        .join(
            models.SignalJourneyAudiences,
            models.SignalJourneyAudiences.audienceId
            == models.SignalJourneyAudienceConstraints.audienceId,
        )
        .join(
            models.SingalJourneySources,
            models.SingalJourneySources.sourceId
            == models.SignalJourneyAudienceConstraints.sourceId,
        )
        .join(
            models.SignalJourneyConstraintType,
            models.SignalJourneyConstraintType.constraintTypeId
            == models.SignalJourneyAudienceConstraints.constraintTypeId,
        )
        .all()
    )