Selecting records not found in other list of records

Question

Scenario

Players take part in events. They should provide information if they're going to attend an event or not.

Problem to be solved

I want to select players that haven't provided information, if they plan to attend specific event.

Your goal

As being a beginner in the technologies used, I would appreciate your validation and recommendation for improvement of the solution suggested by me below.

Solution

Technologies: python, postgreSQL, and Pony ORM

Entity model in Pony ORM:

class Event(db.Entity):
    _table_ = "event"
    start = Required(date)
    players = Set("Attendance")


class Player(db.Entity):
    _table_ = "player"
    first_name = Optional(str)
    last_name = Required(str)
    phone_number = Required(str)
    email = Required(str)
    events = Set("Attendance")


class Attendance(db.Entity):
    _table_ = "attendance"
    event = Required(Event)
    player = Required(Player)
    status = Required(bool)
    PrimaryKey(event, player)

Idea:

1. Get list of players that provided the information if they attend the event
2. Get list of players that are not in list created in 1.

Current implementation of the idea:

players = select(p for p in Player if p not in select(p for p in Player
                 for a in Attendance if p == a.player and a.event == next_event))

Answer 1

It is possible to refactor your query to make it more simple. At first, we can replace explicit join of Player and Attendance inside the inner query to implicit join via attribute access:

select(p for p in Player if p not in select(p for p in Player
       for attendance in p.events if attendance.event == next_event))

To simplify query further we can use attribute lifting by writing expression p.events.event:

select(p for p in Player if p not in select(
       p for p in Player if next_event in p.events.event))

p.events expression returns a set of Attendance records. In Pony when you have a set instance this set has all attributes of its items, and when you access such attribute you will get a set of all values of corresponding items attribute. The value of expression p.events.event will be the set of all Event objects linked with particular player.

The next step to simplify the query is to replace generators to lambdas. This way the query looks a bit shorter:

Player.select(lambda p: p not in Player.select(
    lambda p: next_event in p.events.event))

But the biggest simplification can be achieved if we realize that the inner query is unnecessary and rewrite query as:

Player.select(lambda p: next_event not in p.events.event)

I think this is the most concise way to write this query using PonyORM