How to achieve simple subquery in peewee without join

Question

I would like to have a simple subquery. One I would like to re-use in several places. Some of those would be joins, some would not.

SQL code would be like this

    SELECT IF(x, y, z) as foo, table.*
    FROM TABLE
    WHERE condition

And then it's used in many places, joining and where'ing by foo.

Sometimes simply like this:

SELECT * FROM
(
    SELECT IF(x, y, z) as foo, table.*
    FROM TABLE
    WHERE condition
) WHERE (foo > 100)

Sometimes more complex, like grouping, joining.

However, I find it quite hard to do in peewee.

I figured out I can do this if I use joins

query1 = table1.select(...).where(...)
query2 = table2.select(...).join(query1, on=(...))...

This would work

query1 = table1.select(...).where(...)
query2 = query1.select(...).join(table2, on=(...))...

This would also work

However, if I just select from query1, it doesn't work. Exact code that fails:

    query = tables.Payments.select(fn.IF(tables.Payments.payment_status > 0, tables.Payments.payment_amount, -tables.Payments.payment_amount).alias("x")).where(tables.Payments.payment_amount > 200)
    query2 = query.select().where(query.c.x < 0)

I expect query2 to just be a select from Payments where x, calculated according to condition before, is less than 0, but instead it produces bogus SQL code

SELECT  FROM `payments` AS `t1` WHERE ((`t1`.`payment_amount` > 200) AND (`t2`.`x` < 0))

Which is obviously malformed and doesn't execute

How do I do this? Is this even possible in peewee?

I know I could write "where()" and replicate my condition there, but that's bad practice, because it's copypasting code, and what if I want to change that condition later? Do I re-do it in 10 places?... Surely there's a proper way to do this

PS: As advised, I have altered my code but it produces malformed SQL query again.

My code:

query = tables.Payments.select(fn.IF(tables.Payments.payment_status > 0, tables.Payments.payment_amount, -tables.Payments.payment_amount).alias("x")).where(tables.Payments.payment_amount > 200)
query2 = query.select_from(query.c.x).where(query.c.x < 0)

Resulting query:

SELECT `t1`.`x` FROM (SELECT IF((`t2`.`payment_status` > 0), `t2`.`payment_amount`, `t2`.`payment_amount` DESC) AS `x` FROM `payments` AS `t2` WHERE (`t2`.`payment_amount` > 200)) AS `t1` WHERE (`t1`.`x` < 0)

As you see, instead of doing a minus operation, it adds DESC which is obviously not right.

How to fix this?

Answer 1

Here is an example of wrapping a subquery using select_from():

db = SqliteDatabase(':memory:')

class Reg(db.Model):
    key = TextField()

db.create_tables([Reg])

Reg.create(key='k1')
Reg.create(key='k2')
Reg.create(key='k3')

subq = Reg.select(Reg.key.alias('foo'), Reg)

query = subq.select_from(subq.c.foo).where(subq.c.foo.in_(['k1', 'k3']))
for row in query:
    print(row.foo)

# k1
# k3

Another example, this is basically what select_from() does under-the-hood:

query = Select([subq], [subq.c.foo]).bind(db)
for row in query:
    print(row)
# {'foo': 'k1'}
# {'foo': 'k2'}
# {'foo': 'k3'}

For your last/most-recent edit to your issue, replace unary minus with X * -1:

query = (Payments.select(fn.IF(
    Payments.payment_status > 0,
    Payments.payment_amount,
    Payments.payment_amount * -1).alias("x")
).where(Payments.payment_amount > 200)
query2 = query.select_from(query.c.x).where(query.c.x < 0)