[amalashkevich]

Hi Mike,

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.

[zzzeek]

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.

[amalashkevich]

There is no need to wrap it with a query. You can just add a filter:

    mynewquery = myquery.filter(lambda c: c.name > 'G')

The new query will produce the following SQL:

    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

[zzzeek]

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.

As 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.