Adding multiple values for db column with relationship

Question

I have an interesting problem. I have a DB model with two tables:

class User(db.Model):
    __tablename__ = 'Users'
    id = db.Column(db.Integer, primary_key=True, unique=True)
    first_name = ....
    last_name = ....
    email = ....
    orders = db.relationship("Order")

class Order(db.Model):
    __tablename__ = 'Orders'
    user_id = db.Column(db.Integer, db.ForeignKey('Users.id'))
    order = db.Column(db.String(200), unique=False)

Now i have a form which has the "first_name", "last_name" etc. fields to populate the User class but i also have a textarea field for "orders".

When the form is submitted i am doing the following:

new_user = User(fist_name=form.first_name.data, last_name=....)
if form.orders.data:
    order = Order(order=form.orders.data)
    new_user.orders = [order]
    db.session.add(order)
db.session.add(new_user)
db.session.commit()

Now this works just fine but now i want to split the "orders" field by comma and add each one into the db for the same User. I tried:

new_user = User(fist_name=form.first_name.data, last_name=....)
if form.orders.data:
    separate_data = str(form.orders.data).split(',')
    for item in separate_data:
        item = item.lstrip()
        order = Order(order=item)
        new_user.orders = [order]
        db.session.add(order)
db.session.add(new_user)
db.session.commit()

The problem is that only the last Order has the user_id foreign key of the "new_user" the ones before have an empty row in the column.

The second thing i tried is creating a dictionary:

new_user = User(fist_name=form.first_name.data, last_name=....)
if form.orders.data:
    n = 0
    orders_dict = {}
    separate_data = str(form.orders.data).split(',')
    for item in separate_data:
        n += 1
        item = item.lstrip()
        orders_dict[f'order{n}'] = item
    for key in orders_dict:
        item = item.lstrip()
        key = Order(order=orders_dict[key])
        new_user.orders = [key]
        db.session.add(key)
db.session.add(new_user)
db.session.commit()

But that has the same issue as before. Only the last order is added with a value in the "user_id" column while the ones before it have an empty row.

I cant seem to figure this out so any help would be appreciated.

Answer 1

The way you have it, at every iteration of your loop you are replacing the relationship (it is a list) with a new list that has only one element [order].

You have to instead append order at every iteration.

Using new_user.orders.append(order) instead of new_user.orders = [order] should give you what you want.