sqlite 将SQL转换为Peewee

agxfikkp  于 2022-11-14  发布在  SQLite
关注(0)|答案(1)|浏览(132)

如何将此转换为Peewee查询?

_, storage, spaceLeft = db.execute('''
    SELECT session, storage, storage - count(questID) FROM Hypos
    INNER JOIN Quests ON Hypos.hypoID = Quests.hypoID
    WHERE Hypos.hypoID = ?1 AND session = ?2
    GROUP BY session
    UNION SELECT NULL, max(storage), storage FROM Hypos
    WHERE Hypos.hypoID = ?1
    ORDER BY session DESC LIMIT 1
    ''', (body.hypo_id, session)
).fetchone()

我不明白如何在Peewee中创建UNION SELECT。我的尝试是:

_, storage, spaceLeft = (db_models.Hypos
        .select(db_models.Quests.session, db_models.Hypos.storage, db_models.Hypos.storage-fn.COUNT(db_models.Quests.questID))
        .join(db_models.Quests, on=(db_models.Hypos.hypoID == db_models.Quests.hypoID), attr='quests')
        .where(db_models.Hypos.hypoID==body.hypo_id, db_models.Quests.session==session)
        .group_by(db_models.Quests.session) 
    ) | (db_models.Hypos.select(None, fn.MAX(db_models.Hypos.storage), db_models.Hypos.storage)
        .where(db_models.Hypos.hypoID==body.hypo_id)
        .order_by(db_models.Quests.session.desc()).limit(1)
        )

我得到了:
第1个ORDER BY术语与结果集中的任何列都不匹配,但ORDER_BY问题没有足够的值可解压缩(预期为%3,实际为%2)

zbsbpyhn

zbsbpyhn1#

试着这样做:

lhs = (Hypo
       .select(Quest.session, Hypo.storage, Hypo.storage - fn.COUNT(Quest.questID))
       .join(Quest)
       .where(
           (Hypo.hypoID == hypo_id) &
           (Quest.session == session))
       .group_by(Quest.session))

rhs = (Hypo
       .select(Value(None), fn.MAX(Hypo.storage), Hypo.storage)
       .where(Hypo.hypoID == hypo_id))

union = (lhs | rhs).order_by(SQL('1').desc()).limit(1)
_, storage, spaceLeft = union.scalar(as_tuple=True)

生成的SQL:

SELECT "t1"."session", "t2"."storage", ("t2"."storage" - COUNT("t1"."questID")) 
FROM "hypo" AS "t2" 
INNER JOIN "quest" AS "t1" ON ("t1"."hypo_id" = "t2"."hypoID") 
WHERE (("t2"."hypoID" = ?) AND ("t1"."session" = ?)) 
GROUP BY "t1"."session" 
UNION
SELECT ?, MAX("t3"."storage"), "t3"."storage" 
FROM "hypo" AS "t3" 
WHERE ("t3"."hypoID" = ?) 
ORDER BY 1 DESC LIMIT ?

相关问题