使SQLAlchemy在联接中使用所需的表

Question

我正在编写一个使用多个外部联接的查询，并遇到了SQLAlchemy选择错误表的问题

这是我想要的问题-

SELECT 
    vocab_term.id,
    COALESCE(max(vocab_session_entry.question_level), 0) AS score
FROM 
    vocab_set
JOIN
    vocab_term
    on vocab_term.set_id = vocab_set.id
LEFT JOIN
    vocab_session
    on vocab_session.set_id = vocab_set.id
LEFT JOIN
    vocab_session_entry
    on vocab_session_entry.session_id = vocab_session.id and vocab_session_entry.term_id = vocab_term.id
WHERE 
    vocab_set.id = %(id_1)s
    AND vocab_session.user_id = %(user_id_1)s
GROUP BY
    vocab_set.id, vocab_term.id, vocab_session.user_id;

下面是我的SQLAlchemy实现：

db.session.query(VocabTerm.id, coalesce(func.max(VocabSessionEntry.question_level)))
    .join(VocabTerm, VocabTerm.set_id==VocabSet.id)
    .join(VocabSession, VocabSession.set_id==VocabSet.id, isouter=True)
    .join(VocabSessionEntry, and_(VocabSessionEntry.session_id, VocabSessionEntry.term_id==VocabTerm.id), isouter=True)
    .filter(VocabSet.id==vocab_set_id)
    .filter(VocabSession.user_id==user_id)
    .group_by(VocabSet.id, VocabTerm.id, VocabSession.user_id)
    .all()

以下是我最终得到的结果（由SQLAlchemy制作：

SELECT
    vocab_term.id AS vocab_term_id,
    coalesce(max(vocab_session_entry.question_level)) AS score
FROM
    vocab_session_entry
JOIN
    vocab_term ON vocab_term.set_id = vocab_set.id
LEFT OUTER JOIN
    vocab_session
    ON vocab_session.set_id = vocab_set.id
LEFT OUTER JOIN
    vocab_session_entry
    ON vocab_session_entry.session_id AND vocab_session_entry.term_id = vocab_term.id
WHERE
    vocab_set.id = %(id_1)s
    AND vocab_session.user_id = %(user_id_1)s
GROUP BY
    vocab_set.id, vocab_term.id, vocab_session.user_id]

唯一的区别是SQLAlchemy使用vocab_session_条目而不是vocab_集作为其FROM表。有什么方法可以让SQLAlchemy使用vocab_集作为from表

Answer 1

对于遇到此问题的任何其他人-可以使用Query.select_from（）显式设置from子句Docs