mysql - SQLALCHEMY 查询与映射表的多对多关系

Question

我有一个数据库模型(见下文)。我正在开发一个项目，该项目应该根据用户分配的类别过滤查询结果 - 可以有多个与用户关联的类别。换句话说，我想向 User 显示与他/她分配的类别相匹配的 Articles。 文章还可以与多个类别相关联。如何使用 sqlalchemy 查询实现此目的？

categories_users = db.Table(
    'categories_users',
    db.Column('user_id', db.Integer(), db.ForeignKey('user.id')),
    db.Column('category_id', db.Integer(), db.ForeignKey('category.id'))
)

categories_articles = db.Table(
    'categories_articles',
    db.Column('article_id', db.Integer(), db.ForeignKey('article.id')),
    db.Column('category_id', db.Integer(), db.ForeignKey('category.id'))
)

class Category(db.Model):
    id = db.Column(db.Integer(), primary_key=True)
    name = db.Column(db.String(80), unique=True)
    articles = db.relationship('Article', secondary=categories_articles,
        backref=db.backref('categories', lazy='dynamic'))
    def __str__(self):
        return self.name

class Article(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(255))
    def __str__(self):
        return self.name

class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    email = db.Column(db.String(255), unique=True, nullable=False)
    categories = db.relationship('Category', secondary=categories_users,
        backref=db.backref('users', lazy='dynamic'))
    def __str__(self):
        return self.email

Answer 1

在正常的 m2m 中，您有一个关联表，其中两个实体之间的连接路径由外键明确定义。因此，您只需告诉 sqlalchemy 要使用哪个表来关联其他两个表，它就会完成其余的工作(正如您在此处所做的:secondary=categories_articles)。将用户连接到文章稍微更复杂。

您希望User和Article之间存在多对多的关系，这样您就可以根据共同的类别获取用户感兴趣的文章。或者简单地:

用户 <===> 涉及类别的一些连接路径 <===> 文章。

我们的 user 表有一个指向 categories_users 表的外键，而我们的 categories_articles 表有一个指向 articles< 的外键表。最重要的是，categories_users 表和 categories_articles 表都有 category_id 字段，因此我们可以将这两个表连接在一起。

因此，您可以使用以下代码将 articles 关系添加到您的 User 模型:

articles = db.relationship(
    'Article',
    secondary=\
        'join(categories_users, categories_articles, '
        'categories_users.c.category_id==categories_articles.c.category_id)'
)

访问 User.articles 属性会发出以下 SQL:

SELECT article.id AS article_id, article.name AS article_name
FROM article, categories_users 
INNER JOIN categories_articles 
ON categories_users.category_id = categories_articles.category_id
WHERE %(param_1)s = categories_users.user_id 
AND article.id = categories_articles.article_id

可以清楚地看到正在发生的事情。 categories_users 与 categories_articles 连接，有效地创建一个新表，其中 user 行与公共(public)类别的 article 行组合在一起。然后，SQLAlchemy 可以将其视为具有单个关联表的“正常”多对多关系，并利用现有外键将关系的其余部分拼凑在一起并构造必要的查询。