从 4 个不同表中获取数据

在一个数据库中存在 4 个模型：user、interest、user_interest 和 offer。需要获取订阅特定兴趣的所有用户的所有优惠。

表结构如下：

表格	列	关系
user	id (pk)
user	name
interest	id (pk)
interest	name
user_interest	id (pk)	user_id (fk)
user_interest	interest_id (fk)
offer	id (pk)
offer	interest_id (fk)

现有关系：

class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(80), unique=True, nullable=False)
    interests = db.relationship('user_interest', backref='app_user', lazy='dynamic', uselist=True)

2、解决方案

1. 设置关系

在 user 和 interest 模型中设置关系如下：

class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(80), unique=True, nullable=False)
    interests = db.relationship('user_interest', backref='app_user', lazy='dynamic', uselist=True)

class Interest(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(80), unique=True, nullable=False)
    users = db.relationship('user_interest', backref='interest', lazy='dynamic', uselist=True)

2. 获取数据

可以使用以下查询获取数据：

user = User.query.get(user_id)
interests = user.interests.all()
offers = []
for interest in interests:
    offers += Offer.query.filter_by(interest_id=interest.id).all()

3. 关于 user_interest 表的疑问

user_interest 表是必要的，因为它建立了 user 和 interest 模型之间的关系。如果没有 user_interest 表，无法知道哪些用户订阅了哪些兴趣。

4. 代码例子

from sqlalchemy import create_engine, Column, Integer, String
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base

engine = create_engine('sqlite:///database.db')
Session = sessionmaker(bind=engine)
session = Session()

Base = declarative_base()

class User(Base):
    __tablename__ = 'user'
    id = Column(Integer, primary_key=True)
    name = Column(String(80), unique=True, nullable=False)
    interests = relationship('user_interest', backref='app_user', lazy='dynamic', uselist=True)

class Interest(Base):
    __tablename__ = 'interest'
    id = Column(Integer, primary_key=True)
    name = Column(String(80), unique=True, nullable=False)
    users = relationship('user_interest', backref='interest', lazy='dynamic', uselist=True)

class UserInterest(Base):
    __tablename__ = 'user_interest'
    id = Column(Integer, primary_key=True)
    user_id = Column(Integer, ForeignKey('user.id'))
    interest_id = Column(Integer, ForeignKey('interest.id'))

class Offer(Base):
    __tablename__ = 'offer'
    id = Column(Integer, primary_key=True)
    interest_id = Column(Integer, ForeignKey('interest.id'))

Base.metadata.create_all(engine)

user = User(name='John Doe')
session.add(user)
session.commit()

interest1 = Interest(name='Sports')
interest2 = Interest(name='Music')
session.add_all([interest1, interest2])
session.commit()

user_interest1 = UserInterest(user_id=user.id, interest_id=interest1.id)
user_interest2 = UserInterest(user_id=user.id, interest_id=interest2.id)
session.add_all([user_interest1, user_interest2])
session.commit()

offer1 = Offer(interest_id=interest1.id)
offer2 = Offer(interest_id=interest2.id)
session.add_all([offer1, offer2])
session.commit()

# 获取订阅特定兴趣的所有用户的所有优惠
user = session.query(User).get(user.id)
interests = user.interests.all()
offers = []
for interest in interests:
    offers += session.query(Offer).filter_by(interest_id=interest.id).all()

print(offers)

输出结果：

[<Offer 1>, <Offer 2>]