Orders for Commissioned Employees Only

Requirement - Orders for Commissioned Employees Only

We want to ensure that the EmployeeId for an order references an Employee that IsCommissioned.

Organizational Constraint: cannot alter Employees schema

As we'll see below, we will want to declare an Employee.order_count rule. But, this particular table is used by non-SQLAlchemy applications that do not enforce new rules.

Logic

Model

Since we cannot alter the schema, we define a hybrid attribute. We add the following to the nw/db/models.py for the Employees class:

    @hybrid_property
    def order_count(self):
        if not hasattr(self, "_order_count"):
            self._order_count = self.order_count_sql
        return self._order_count

    @order_count.setter
    def order_count(self, value):
        self._order_count = value

We must also add the following - it occurs later in the code to avoid making references to the not-yet-defined Order class:

Employee.order_count_sql = column_property(
    select([func.count(Order.Id)]). \
    where(Order.Id == Order.EmployeeId))

Rule

The addition of the setter enables Logic Bank to maintain the value as orders are added. We define the rule just as if the order_count were a persistent attribute:

    Rule.constraint(validate=Employee,
                    as_condition=lambda row: row.IsCommissioned == 1 or row.order_count == 0,
                    error_msg="{row.LastName} is not commissioned - cannot have orders")

    Rule.count(derive=Employee.order_count, as_count_of=Order)