I'd like to do a query like this:
select t1.label, count(t2.id)
from t1 left join t2
on t1.id = t2.fk [and t2.field_1 = 'x' and ...]
[where t1.field_a = 'y' and ...]
group by t1.label;
My initial attempt was to do
sub_query = pony.orm.select(x for x in t2)
apply_sub_filter(sub_query, ...)
query = pony.orm.select((y.label, pony.orm.count(sub_query)) for y in t1)
apply_filter(query, ...)
That doesn't work (not even when removing the filters)
According to the 4th example in this doc reusing queries is supported, so I assumed the count is what throw is off here.
It works when putting it all in one select()
pony.orm.select(y.label, pony.orm.count(x for x in t2 if x.fk == y.id [and ...]) for x in t1 [if ....])
this works as expected but it hardcodes both set of filters, and I need them to be dynamic.
I could write every possible combination of filter and choose which query to run based on the filter I get at runtime, but I think everyone will agree it defeats the purpose of using an ORM
As a side note: The documentation states that running this kind of subqueries (like the last example) would be optimised by ponyorm as a left join, but when I observe what queries run in database, there is no left join, only subqueries, which is likely going to be very slow on larger datasets.
How can I make this works ? I will also need to support pagination later on.
Any chance to optimise this to have ponyorm use left join instead of subqueries ?