Building a query incrementally in Pony ORM

Question

I am evaluating switching from peewee to Pony ORM. One nice thing that was available in peewee was the ability to compose a query from parts like this:

def Entry(BaseModel):
    # peewee fields go here

def where_entry_category(category, recurse=False):
    """ Generate a where clause for a particular category """

    if category or not recurse:
        cat_where = (Entry.category == str(category))

        if recurse:
            # We're recursing and aren't in /, so add the prefix clause
            cat_where = cat_where | (
                Entry.category.startswith(str(category) + '/'))
    else:
        cat_where = True

    return cat_where

query = Entry.select().where(where_entry_category("test"))

The way that this works is that the various operator overloads on a peewee model type simply return a tree of query components, and these subtrees can be composed by further operator overloads. It's also a simple matter to have multiple query components which get chained together with the & or | operators, e.g. model.Entry.select().where(some_test() & some_other_test()). This is very useful as many of my filtering queries are composed in modular ways, and most of the underlying query parts are reused often and many are nontrivial (such as the above example).

However, in Pony ORM, there appears to only be the (quite clever!) AST generator parser and raw SQL. Since the raw SQL form does not make it straightforward for me to pass in the necessary query parts, I would prefer to use some higher-level query-building functionality if at all possible.

If I try defining the query parts as methods on the model, e.g.:

class Entry(db.Entity):
    ...
    def in_category(self, category, recurse=False):
        # return a test on the parameters

orm.select(entry for entry in model.Entry if entry.in_category('foo', True)) 

I get NotImplementedError, unsurprisingly.

Is there a mechanism for building a query expression from existing parts, to be passed into the SQL query builder? (Perhaps by building the AST myself and pass that into the relevant part of Pony, or having a mechanism where I pass a query along to be filtered by another subquery.)

Answer 1

In PonyORM there are two ways how you can compose query step-by-step. The first is a filter method of a query:

def where_entry_category(query, category, recourse)
    if category:
        category = str(category)
        if recurse:
            query = query.filter(lambda x: x.category == category or
                                 x.category.startswith(category + '/')
        else:
            query = query.filter(lambda x: x.category == category)
    return query

query = Entry.select()
query = where_entry_category(query, "test")

Starting from the release 0.7.6 it is also possible to use previous query as a source for a new query:

def where_entry_category(query, category, recourse)
    if category:
        category = str(category)
        if recurse:
            query = select(x for x in query
                           if x.category == category or
                              x.category.startswith(category + '/'))
        else:
            query = select(x for x in query if x.category == category)
    return query

The only problem you can encounter is if you want to gradually build or clause with variable number of subexpressions, at this moment Pony does not have API for that. May be we'll add a possibility to add subexpressions to or clause gradually in the future releases.