python - Need help making a ordering system (postgres, flask-sqlalchemy)

Question

Problem

The following database models:

Menu Table:

 class Menu(db.Model):
    __tablename__ = "menu"

    id = db.Column(db.Integer(), primary_key=True)
    title = db.Column(db.String())
    price = db.Column(db.Integer())
    vegetarian = db.Column(db.Boolean())

    order = db.relationship("Order", backref="menu", lazy="dynamic")

User Table:

class User(db.Model):
    __tablename__="users"

    id = db.Column(db.Integer, primary_key=True)
    email = db.Column(db.String(), nullable=False, unique=True)
    password = db.Column(db.String(), nullable=False)
    first_name = db.Column(db.String(), nullable=False)
    last_name = db.Column(db.String(), nullable=False)
    admin = db.Column(db.Boolean(), default=False)


    order = db.relationship("Order", backref="users")

Order Table

class Order(db.Model):
    __tablename__ = "user_order"

    id = db.Column(db.Integer, primary_key=True)
    date_time = db.Column(db.DateTime(timezone=True), nullable=False, server_default=func.now())
    menu_id = db.Column(db.Integer, db.ForeignKey("menu.id"), nullable=False)
    user_id = db.Column(db.Integer, db.ForeignKey("users.id"), nullable=False)
    

The problem resides in the Order table where a user will purchase an/multiple item/s and thus be recorded as a transaction in the order table. The problem is how will I record multiple item being purchase in a single transaction with out the order Id incrementing.

E.G

Order Id | User Id | Menu Id| History
    1.        1.        1.    11/11/11
    1.        1.        2.    11/11/11
    2.        1.        5.    14/11/11

Overall I want the user to have the ability to view their transaction history.

Is there a way to do this? Is there a simpler way?

@order.route("/" methods=["POST"])
def order_menu():
    #Something

How will I go about doing a post request ordering more then 1 item?

Answer 1

I did similar thing just few days ago , i started to work in my friends company he needed some help with IT stuff. I will paste you my models code ( its quiet spaghettii was in hurry had to finish the ordering system in less than 8 hours...)

class Article(db.Model):
    __tablename__ = 'articles'
    id = db.Column(db.Integer , primary_key=True)
    name = db.Column(db.String, unique=True)
    type = db.Column(db.String)




class Order(db.Model):
    __tablename__ = 'orders'
    id = db.Column(db.Integer, primary_key=True)
    bn = db.Column(db.String)
    commission = db.Column(db.String)
    week = db.Column(db.String)
    date = db.Column(db.String)
    napomena = db.Column(db.TEXT)
    hitno = db.Column(db.Boolean, default=False)

    placement = db.relationship('Placement', cascade="all, delete, delete-orphan")


class Placement(db.Model):
    __tablename__ = 'placements'
    id = db.Column(db.Integer, primary_key=True)
    qty = db.Column(db.Integer)
    name = db.Column(db.String)

    order_id = db.Column(db.Integer, db.ForeignKey('orders.id'))

So here is the deal , i needed the same thing it could be achieved on another way but this was the most simple way i figured out. So i have a model ARTICLE ( wich are in general items that are going to be sold ) , then i have table ORDER that has relationship with table PLACEMENT wich saves the name of item and quantity . Later on i just query over order and call the order.placement to see all the items that are related to one order.

I hope u understand my example i think its fairly similar analogy for you too..

Good luck !