How to do coalesce on aggregation?

[dekoza]

I want to annotate results with the last time a page was displayed and then sort in descendant order. Unfortunately when there is no such date I get None which messes the order by juping in front.
When I tried to use max(i.created) if i.created else datetime(1970,1,1) I got Expression is too complex to decompile error

[kozlovsky]

Thank you for reporting! I agree that Pony should support coalesce function, and we just added support of it in b63634b and ccbcf8c. Starting from release 0.7.3 you will be able to write

coalesce(max(i.created), datetime(1970, 1, 1))

Regarding Expression is too complex to decompile error, that arises because at this moment Pony decompiler cannot handle if-expression bytecode. In order to use if-expressions, you need to pass generator text as a string. By the way, you query looks incorrect: if-expression should be inside max function:

max(i.created if i.created is not None else datetime(1970, 1, 1))

Also, I'm not sure the query is logically correct. When you use aggregation, you need to have some grouping, and it is not clear to me what you want to place to GROUP BY section of your SQL query

[dekoza]

That is awesome news! Thank you! Please disregard the second part, I was indeed using Pony in the wrong way ;)