Convert SQL to peewee

Question

How to convert this to peewee query?

_, 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()

I don't understand how to create UNION SELECT in peewee. My attempt:

_, 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)
        )

I got :

1st ORDER BY term does not match any column in the result set without order_by problem is not enough values to unpack (expected 3, got 2)

score 0 · Answer 1 · answered Nov 09 '22 at 14:12

Try something like this:

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)

The resulting 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 ?

Convert SQL to peewee

1 Answers1