我正在试着做这个查询

subsidiaries = self.con.query(Subsidiary)\
                    .options(joinedload(Subsidiary.commerce)\
                             .joinedload(Commerce.commerce_tags))\
                    .filter(CommerceTag.tag_id == id)

但它不起作用,所以要解释:
表之间的关系是:
python - 尝试按中间表进行过滤-LMLPHP
我只想获得所有商业的子公司,这些子公司都有一个特定的标签,我想用子公司有一个基类(我知道如果我使用Commerce会更容易),原因是我用下面的格式将它转换成一个json值:
"subsidiaries": [
    {
        "id": 1,
        "name": "some_name",
        "commerce": {
            "id": 1,
            "name": "Commerce Name"
        }
    }
]

好吧,我可以用Commerce has基类进行查询,但我认为,也许通过迭代Commerce来获取子公司比在查询中进行更昂贵。
我不想加载CommerceTag,但我在joinedload中有它,因为它不适用于Join方法。
我需要一些帮助:(

最佳答案

这样试试:

subsidiaries = session.query(Subsidiary)\
                .join(Subsidiary.commerce)\
                .join(Commerce.commerce_tags)\
                .filter(CommerceTag.id == id)

语句生成如下查询:
SELECT subsidiary.id AS subsidiary_id, subsidiary....
FROM tag, subsidiary
LEFT OUTER JOIN commerce AS commerce_1
    ON subsidiary.id = commerce_1.subsidiary_id
LEFT OUTER JOIN (
    tags_by_commerce AS tags_by_commerce_1
    JOIN tag AS tag_1 ON tag_1.id = tags_by_commerce_1.id_tag
) ON commerce_1.id = tags_by_commerce_1.id_commerce
WHERE tag.id = :id_1

所以标签过滤没有效果。
我提议的声明产生了这样的结果:
SELECT subsidiary.id AS subsidiary_id, subsidiary.name AS subsidiary_name
FROM subsidiary
JOIN commerce ON subsidiary.id = commerce.subsidiary_id
JOIN tags_by_commerce AS tags_by_commerce_1 ON commerce.id = tags_by_commerce_1.id_commerce
JOIN tag ON tag.id = tags_by_commerce_1.id_tag
WHERE tag.id = :id_1

10-01 00:45