IN clause in pony orm query

Question

How can I use subquery in pony orm for query like

SELECT * 
FROM  child_table
WHERE child_table.masterno IN (SELECT masterno
                    FROM   mastertable 
                    WHERE  mastertable.recorddate > SYSDATE -1)

In another word. I wanted to do somthing like

master_result = target_model.mastertable.select()\
                .filter(lambda mt: mt.recorddate > DATE)

output = target_model.child_table.select()\
           .filter(lambda ct: ct.masterno in master_result)

I got error

pony.orm.sqltranslation.IncomparableTypesError: Incomparable types 'int' and 'mastertable' in expression: ct.masterno in master_result

where master_result is

print(type(master_result))

<'pony.orm.core.QueryResult'>

Answer 1

Yes, by Pony API you will get QueryResult object from select() and filter() functions. If you want to have entities from that query you could change your line master_result = target_model.mastertable.select()\ .filter(lambda mt: mt.recorddate > DATE)
to
master_result = target_model.mastertable.select()\ .filter(lambda mt: mt.recorddate > DATE)[:] <- this will fetch objects

Another way you can cast QueryResult to list like results = list(master_result). Also you can iterate over QueryResult object and you will get Entity objects as item. for mastertable in mastertable.select(): mastertable.recordtable = *smth*