how to populate sqlalchemy columns' values automatically

Question

I am working on e-commerce app's database, I have a One to Many relationship - using SQLAlchemy relationship() - between the Order table(parent) and the OrderItem table(children).

Database:

class Order(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    customer_id = db.Column(db.Integer, db.ForeignKey('customer.id'), nullable=False)
    total = db.Column(db.Integer, nullable=False)
    submitted_on = db.Column(db.DateTime, nullable=False, default=datetime.utcnow)
    delivery_charges = db.Column(db.Integer, nullable=False)
    sub_total = db.Column(db.Integer, nullable=False)  # needs to be calculated automatically
    total_quantity = db.Column(db.Integer, nullable=False)  # needs to be calculated automatically
    order_items = db.relationship('OrderItem', backref='order', lazy=True)

    def __init__(self, customer_id, delivery_charges, sub_total, total_quantity):
        self.customer_id = customer_id
        self.delivery_charges = delivery_charges
        self.sub_total = sub_total
        self.total_quantity = total_quantity
        self.total = delivery_charges + sub_total


class OrderItem(db.Model):
    __tablename__ = "order_item"
    id = db.Column(db.Integer, primary_key=True)
    order_id = db.Column(db.Integer, db.ForeignKey('order.id'), nullable=False)
    product_size_color_id = db.Column(db.Integer, db.ForeignKey('product_size_color.id'), nullable=False)
    sale_id = db.Column(db.Integer, db.ForeignKey('sale.id'), nullable=True, default=None)
    selling_price = db.Column(db.Integer, nullable=False)  # needs to be calculated automatically
    quantity = db.Column(db.Integer, nullable=False)

what I am trying to do is: I want certain columns to be calculated automatically depending on values from other tables as such:

-Order.total_quantity: the sum of each order_item.quantity corresponding to the same Order instance

-Order.sub_total: the sum of each order_item.selling_price corresponding to the same Order instance.

-Order_item.selling_price: ...

I used default to get some columns sat automatically like in Order.submitted_on and used __init__ function in class Order to calculate the Order.total value as answered in this question, However, these two methods only works if the values are withing the class/table itself, my question is: How can I automatically set values for columns depending on other tables' values?!!

I tried the following code on column Order.total_quantity, It filled the column with zeros!

    def __init__(self, customer_id, delivery_charges, sub_total):
        self.customer_id = customer_id
        self.delivery_charges = delivery_charges
        self.sub_total = sub_total
        order_items = self.order_items
        self.total_quantity = sum([i.quantity for i in order_items])
        self.total = delivery_charges + sub_total

Is what I am trying to do possible? How?

Answer 1

The problem is that when you initialize an Order object, there are no associated order_items yet. You couldn't have created any, unless you were guessing at what order_id would be, and that seems pretty risky. Instead of running it on init, why not make an update quantity method to run after you've added all of your items?

For example:

class Order(Model):
    # your code as-is

    def update_total(self):
        self.total_quantity = sum([i.quantity for i in self.order_items])
        self.total = self.delivery_charges + self.sub_total

The method is exactly what you have proposed already, just outside of __init__, because you need to call it after adding items. In fact, I would just remove your __init__ altogether - that way you can change the totals whenever you need (say you need to add an item to an order later after creating it but before closing out), and call update_total() to get everything most up-to-date.

You might also consider creating a method to add an item and use it to both add an item and update your totals at the same time. You can use this to automatically calculate the price for any quantity of item types (which I assume to be ProductSizeColor). I have called this "line_total," but you can use whatever makes sense to you.

class OrderItem(Model):
    __tablename__ = 'order_item'
    id = Column(Integer, primary_key=True)
    order_id = Column(Integer, ForeignKey('order.id'))
    product_size_color_id = Column(Integer, ForeignKey('product_size_color.id'))
    product_size_color = relationship('ProductSizeColor')
    # would it make more sense for the sale to be associated with the order
    # instead of the order item?
    sale_id = Column(Integer, ForeignKey('sale.id'))
    quantity = Column(Integer)
    line_total = Column(Integer)

class Order(Model):
    # other code

    def update_total(self):
        self.total_quantity = sum([i.quantity for i in self.order_items])
        self.sub_total = sum([i.line_total for i in self.order_items])
        self.total = self.delivery_charges + self.sub_total

    def add_item(self, session, psi_id, quantity):
        s = session
        order_item = OrderItem(
            order_id = self.id,
            product_size_color_id = psi_id,
            quantity = quantity
        )
        s.add(order_item)
        line_total = order_item.product_size_color.selling_price * quantity
        order_item.line_total = line_total
        self.update_total()

Then in real time:

s = session()
order = Order(customer_id=customer_id, delivery_charges=delivery_charges,
              sub_total=sub_total)
s.add(order)
order.add_item(s, 1, 10)
order.add_item(s, 2, 8)
s.commit()

We just added 10 of whatever ProductSizeColor number 1 is and 8 of ProductSizeColor number 2, and updated the total number of items, the total for each product, and the total for the entire order.