Query with optional arguments using PonyORM

I'm building an API for a library of teambuilding games. The games have defined properties like type, size and length that I'm storing as many-to-many relationships. The model looks like this:

class Game(db.Entity):
    game_type = Set('Type')
    game_length = Set('Length')
    group_size = Set('GroupSize')
    ...

class Type(db.Entity):  # similar for Length, GroupSize
    game = Set(Game)
    short = Required(str)
    full = Required(str)

They are populated with different types/lengths/size values and then assigned to the different games. This works fine.

I had a hard time figuring out how to let users of the database query for e.g. two of these with the third not given. For example, I want all games with type=race AND size=medium but length=None.

I had built this before in SQL with subqueries and empty strings. This was my first working try with PonyORM:

def get_all_games(**kwargs):
    game_type = kwargs.get('game_type', None)
    group_size = kwargs.get('group_size', None)
    game_length = kwargs.get('game_length', None)

    query = select((g, gt, gs, gl) for g in Game
                                     for gt in g.game_type
                                       for gs in g.group_size
                                         for gl in g.game_length)

    if game_type:
        query = query.filter(lambda g, gt, gs, gl: gt.short == game_type)
    if group_size:
        query = query.filter(lambda g, gt, gs, gl: gs.short == group_size)
    if game_length:
        query = query.filter(lambda g, gt, gs, gl: gl.short == game_length)

    query = select(g for (g, gt, gs, gl) in query)

    result = []

    for g in query:
        this_game = get_game(g)
        result.append(this_game)

    return result

It seems overly complicated to me. Is there a way to do this without tuples packing and unpacking? Maybe with using the variables right away in the query without the if statements?

Solution

You can use exists or in in filter. Also you can use attribute lifting to simplify complex joins:

query = Game.select()

if game_length:
    # example with exists
    query = query.filter(lambda g: exists(
        gl for gl in g.game_length
        if gl.min <= game_length and gl.max >= game_length))

if group_size:
    # example with in
    query = query.filter(lambda g: group_size in (
        gs.value for gs in g.group_sizes))

if game_type:
    # example with attribute lifting
    query = query.filter(lambda g: game_type in g.game_types.short)