Reputation: 21
Context: I'm making an auctioning website for which I am using Flask-SQLAlchemy. My tables will need to have a many-to-many relationship (as one artpiece can have many user bids and a user can bid on many artpieces)
My question is: it is possible to add another column to my joining table to contain the id of the user bidding, the id of artpiece that they are bidding on and also how much they bid? Also if yes, how would I include this bid in the table when I add a record to said table?
bid_table = db.Table("bid_table",
db.Column("user_id", db.Integer, db.ForeignKey("user.user_id")),
db.Column("item_id", db.Integer, db.ForeignKey("artpiece.item_id"))
)
class User(db.Model):
user_id = db.Column(db.Integer, unique=True, primary_key=True, nullable=False)
username = db.Column(db.Integer, unique=True, nullable=False)
email = db.Column(db.String(50), unique =True, nullable=False)
password = db.Column(db.String(60), nullable=False)
creation_date = db.Column(db.DateTime, default=str(datetime.datetime.now()))
bids = db.relationship("Artpiece", secondary=bid_table, backref=db.backref("bids", lazy="dynamic"))
class Artpiece(db.Model):
item_id = db.Column(db.Integer, unique=True, primary_key=True, nullable=False)
artist = db.Column(db.String(40), nullable=False)
buyer = db.Column(db.String(40), nullable=False)
end_date = db.Column(db.String(40))
highest_bid = db.Column(db.String(40))
Upvotes: 1
Views: 563
Reputation: 1618
It is possible to do this with SQL Alchemy, but it's very cumbersome in my opinion.
SQLAlchemy uses a concept called an Association Proxy to turn a normal table into an association table. This table can have whatever data fields you want on it, but you have to manually tell SQLAlchemy which columns are foreign keys to the other two tables in question.
This is a good example from the documentation.
In your case, the UserKeyword table is the association proxy table that you want to build for your user/bid scenario.
The special_key
column is the arbitrary data you would store like the bid amount.
from sqlalchemy import Column, Integer, String, ForeignKey
from sqlalchemy.ext.associationproxy import association_proxy
from sqlalchemy.orm import backref, declarative_base, relationship
Base = declarative_base()
class User(Base):
__tablename__ = 'user'
id = Column(Integer, primary_key=True)
name = Column(String(64))
# association proxy of "user_keywords" collection
# to "keyword" attribute
keywords = association_proxy('user_keywords', 'keyword')
def __init__(self, name):
self.name = name
class UserKeyword(Base):
__tablename__ = 'user_keyword'
user_id = Column(Integer, ForeignKey('user.id'), primary_key=True)
keyword_id = Column(Integer, ForeignKey('keyword.id'), primary_key=True)
special_key = Column(String(50))
# bidirectional attribute/collection of "user"/"user_keywords"
user = relationship(User,
backref=backref("user_keywords",
cascade="all, delete-orphan")
)
# reference to the "Keyword" object
keyword = relationship("Keyword")
def __init__(self, keyword=None, user=None, special_key=None):
self.user = user
self.keyword = keyword
self.special_key = special_key
class Keyword(Base):
__tablename__ = 'keyword'
id = Column(Integer, primary_key=True)
keyword = Column('keyword', String(64))
def __init__(self, keyword):
self.keyword = keyword
def __repr__(self):
return 'Keyword(%s)' % repr(self.keyword)
Check out the full documentation for instructions on how to access and create this kind of model.
Having used this in a real project, it's not particularly fun and if you can avoid it, I would recommend it.
https://docs.sqlalchemy.org/en/14/orm/extensions/associationproxy.html
Upvotes: 1