Laravel三张表ORM关联：Project模型获取Category名称

我来帮你搞定这个关联问题！首先明确三张表的关系：Project和Expense是一对多，ExpenseCategory和Expense也是一对多，Project要通过中间的Expense表间接关联到ExpenseCategory。下面我分别用Django ORM和SQLAlchemy这两个主流框架来演示：

Django ORM 方案 #

1. 模型定义 #

先把三个模型写出来，注意外键和反向关联的设置：

from django.db import models

class ExpenseCategory(models.Model):
    cat_id = models.AutoField(primary_key=True)
    name = models.CharField(max_length=100, verbose_name="分类名称")

    def __str__(self):
        return self.name  # 方便在admin或打印时直接显示名称

class Project(models.Model):
    pro_id = models.AutoField(primary_key=True)
    name = models.CharField(max_length=100, verbose_name="项目名称")

    # 自定义方法，获取该项目关联的所有分类名称
    def get_associated_category_names(self):
        # 通过Expense反向关联过滤，distinct()避免同一个分类重复出现
        return ExpenseCategory.objects.filter(
            expense__project=self
        ).distinct().values_list('name', flat=True)

class Expense(models.Model):
    # 关联Project，related_name方便从Project直接访问Expense集合
    project = models.ForeignKey(Project, on_delete=models.CASCADE, related_name="expenses")
    # 关联ExpenseCategory，同样设置related_name
    category = models.ForeignKey(ExpenseCategory, on_delete=models.CASCADE, related_name="expenses")
    amount = models.DecimalField(max_digits=10, decimal_places=2)
    expense_date = models.DateField()

2. 使用方法 #

当你拿到一个Project实例时，直接调用自定义方法就能拿到分类名称：

# 假设你要获取pro_id=1的项目的分类
my_project = Project.objects.get(pro_id=1)
category_names = my_project.get_associated_category_names()
# 输出结果类似：['差旅费', '办公用品', '设备采购']

SQLAlchemy ORM 方案 #

如果用的是SQLAlchemy，模型和关联逻辑如下：

1. 模型定义 #

from sqlalchemy import Column, Integer, String, Decimal, Date, ForeignKey
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship, sessionmaker

Base = declarative_base()

class ExpenseCategory(Base):
    __tablename__ = 'expense_category'
    cat_id = Column(Integer, primary_key=True)
    name = Column(String(100), nullable=False)

    # 反向关联Expense
    expenses = relationship("Expense", back_populates="category")

class Project(Base):
    __tablename__ = 'project'
    pro_id = Column(Integer, primary_key=True)
    name = Column(String(100), nullable=False)

    # 反向关联Expense
    expenses = relationship("Expense", back_populates="project")

    # 自定义方法获取分类名称，需要传入session
    def get_associated_category_names(self, session):
        category_query = session.query(ExpenseCategory.name)\
            .join(Expense)\
            .filter(Expense.project == self)\
            .distinct()
        # 把查询结果转成列表
        return [name[0] for name in category_query.all()]

class Expense(Base):
    __tablename__ = 'expense'
    id = Column(Integer, primary_key=True)
    project_id = Column(Integer, ForeignKey('project.pro_id'))
    category_id = Column(Integer, ForeignKey('expense_category.cat_id'))
    amount = Column(Decimal(10, 2))
    expense_date = Column(Date)

    # 正向关联Project和ExpenseCategory
    project = relationship("Project", back_populates="expenses")
    category = relationship("ExpenseCategory", back_populates="expenses")

2. 使用方法 #

# 假设你已经创建了数据库session
my_project = session.query(Project).filter_by(pro_id=1).first()
category_names = my_project.get_associated_category_names(session)

额外说明 #

如果你的业务场景是一个项目可以直接关联多个分类（不需要通过Expense），那可以考虑给Project和ExpenseCategory建立多对多关系，但根据你描述的“Expense表包含两个外键关联Project和Expense_Category”，显然是通过费用记录间接关联的场景，上面的方案完全匹配你的需求。

内容的提问来源于stack exchange，提问作者Shakib Zaman