amalashkevich

Actually Pony can transform subqueries into JOINs in most of the cases. But when it translates the 'in' operator of a generator expression, it produces a subquery with 'IN', because otherwise the programmer can be confused by the fact that the resulted SQL looks too different from the Python code. Pony allows you to use the 'JOIN' hint in order to make it to use JOINs instead of subquiries. In the example below Pony produces a subquery when it translates the `in` section from the generator:

    >>> from pony.orm.examples.estore import *
    >>> select(c for c in Customer if 'iPad' in c.orders.items.product.name)[:]

    SELECT "c"."id", "c"."email", "c"."password", "c"."name", "c"."country", "c"."address"
    FROM "Customer" "c"
    WHERE 'iPad' IN (
        SELECT "product-1"."name"
        FROM "Order" "order-1", "OrderItem" "orderitem-1", "Product" "product-1"
        WHERE "c"."id" = "order-1"."customer"
          AND "order-1"."id" = "orderitem-1"."order"
          AND "orderitem-1"."product" = "product-1"."id"
        )

But you can tell Pony to use JOIN instead of a subquery by wrapping the 'in' section into a 'JOIN' hint:

    >>> select(c for c in Customer if JOIN('iPad' in c.orders.items.product.name))[:]
	
    SELECT DISTINCT "c"."id", "c"."email", "c"."password", "c"."name", "c"."country", "c"."address"
    FROM "Customer" "c", "Order" "order-1", "OrderItem" "orderitem-1", "Product" "product-1"
    WHERE "product-1"."name" = 'iPad'
      AND "c"."id" = "order-1"."customer"
      AND "order-1"."id" = "orderitem-1"."order"
      AND "orderitem-1"."product" = "product-1"."id"

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

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.

Actually we named our project aprox two years before Django Pony mascot image appeared.

Hi, I am one of Pony ORM authors.

The idea of Pony ORM is to provide a Pythonic way to work with the database. We think that the generator syntax is very concise and convenient.

It is named Pony because a pony is a small, smart and powerful creature - these are the features which our mapper has. Our goal is to provide non-leaky abstraction and good user experience.

Thank you, will check this out.

Pure in this case means that you can use Python syntax in order to query a database. This way a database query looks identical to an iteration over a list of Python objects.

Thanks for pointing this out, we are working on the licensing part now. We are not going to put anyone at risk of the licence misuse. Eventually we are going to have a multiple licensing model allow using Pony ORM for free for non-commercial and open source projects, but in the same provide a commercial license as well.

> are people who find the repo on github directly at risk of violating the license?

Nope, why? Using it under AGPL is a valid option.

Pony gets the code object from a generator, decompiles it to a Python AST and then translates it to a SQL abstract tree.

I'm Alexey Malashkevich, one of the Pony ORM authors. For now Pony ORM runs on CPython only because there is no access to frames in other implementations. "Pure Python" means that you can write queries in term of objects using Python generators.

Thank you for pointing this out, will check this out