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)[:]
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
myquery = select(c for c in Customer if sum(c.orders.total_price) > 1000)
mynewquery = select(c for c in myquery if c.name > 'G')
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.
mynewquery = myquery.filter(lambda c: c.name > 'G')
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) > 1000As far as the "AST allows caching" advantage, over at https://bitbucket.org/zzzeek/sqlalchemy/issue/3054/new-idea-... we're working out a way to give people access to the "lambda: <X>" -> cached SQL in a similar way, if they want it.
at the end of the day the AST idea looks very nifty but IMO is too rigid to translate to SQL in a flexible enough way, and also works against the main reason you use code structures to produce SQL which is composability. When I last saw the Pony creators do a talk, the approach seemed that each time you have a given SELECT, and you'd like to add some extra criterion to it, it pretty much will keep producing subqueries of the original, because each time you can only wrap the AST construct you already have. It similarly had no ability to produce a JOIN - at that time at least, the only way to join things together was by rendering inefficient correlated subqueries. This was asked explicitly.
If they've found a way to resolve these issues while keeping true to the "AST all the way" approach and not dropping into a SQLAlchemy-style approach, that would be great. There's no reason SQLA ORM or Core couldn't be behind a similar approach as well except that nobody's really had the interest in producing it.