Pony had the ability to produce JOINs from the very beginning, but during that presentation we found that Pony produced subqueries for MySQL and that was not very performant, correct. Since than we've improved Pony and now it got a query optimizer which replaces subqueries with efficient JOINs where it is possible.
Here is the query from that presentation:
>>> select(c for c in Customer if sum(c.orders.total_price) > 1000)[:]
The straightforward way is to use a subquery here, but Pony's optimizer produces LEFT JOIN because such query usually has better performance:
SELECT `c`.`id`
FROM `customer` `c`
LEFT JOIN `order` `order-1`
ON `c`.`id` = `order-1`.`customer`
GROUP BY `c`.`id`
HAVING coalesce(SUM(`order-1`.`total_price`), 0) > 1000
In our opinion this is the main advantage of Pony ORM - the possibility to perform semantic transformations of a query in order to produce performat SQL while keeping the text of Python query as high level as possible.
OK, here's something I should understand. Say we start with:
myquery = select(c for c in Customer if sum(c.orders.total_price) > 1000)
I'm inside of a query builder. Based on conditional logic, I also want to alter the above statement to include customer.name > 'G'. Intuitively, I'd do this:
mynewquery = select(c for c in myquery if c.name > 'G')
which will take the original SELECT, wrap it in a whole new SELECT. Right?
Given "myquery", how do I add, after the fact, a simple "WHERE customer.name > 'G"" to the SELECT? Just continuously wrapping in subqueries is obviously not feasible.
SELECT `c`.`id`
FROM `customer` `c`
LEFT JOIN `order` `order-1`
ON `c`.`id` = `order-1`.`customer`
WHERE `c`.`name` > 'G'
GROUP BY `c`.`id`
HAVING coalesce(SUM(`order-1`.`total_price`), 0) > 1000
Well right, this is exactly a SQLAlchemy-style syntax, except a tad more verbose :). This is the "dropping into a SQLAlchemy-style approach" I referred to.
Pony had the ability to produce JOINs from the very beginning, but during that presentation we found that Pony produced subqueries for MySQL and that was not very performant, correct. Since than we've improved Pony and now it got a query optimizer which replaces subqueries with efficient JOINs where it is possible. Here is the query from that presentation:
The straightforward way is to use a subquery here, but Pony's optimizer produces LEFT JOIN because such query usually has better performance: In our opinion this is the main advantage of Pony ORM - the possibility to perform semantic transformations of a query in order to produce performat SQL while keeping the text of Python query as high level as possible.