访问sqlalchemy关联对象中的额外数据

Question

User对象可以以许多不同的方式与Project关联，这是由Job关联表指定的。在我的模板文件中，我试图循环遍历current_user.projects中的所有项目，并在每个项目中指定该项目中该用户的Job是什么.但我的语法不对。

模型

class Project(db.Model):
    id = db.Column(db.Integer(), primary_key=True)
    title = db.Column(db.String(80))
    users = db.relationship("Job", back_populates="project")

class User(db.Model, UserMixin):
    id = db.Column(db.Integer, primary_key=True)
    first_name = db.Column(db.String(155))
    last_name = db.Column(db.String(155))
    email = db.Column(db.String(255), unique=True)
    password = db.Column(db.String(255))
    projects = db.relationship("Job", back_populates="user")

# defines a user's role related to each assigned project
class Job(db.Model):
    __tablename__ = 'job'
    user_id = db.Column(db.Integer, db.ForeignKey('user.id'), primary_key=True)
    project_id = db.Column(db.Integer, db.ForeignKey('project.id'), primary_key=True)
    job = db.Column(db.String(50))
    user = db.relationship("User", back_populates="projects")
    project = db.relationship("Project", back_populates="users")

    def __init__(self, user_id, project_id, job="client"):
        self.user_id = user_id
        self.project_id = project_id
        self.job = job

视图

@app.route('/projects')
@login_required
def projects():
    projects = Project.query.join(Job).filter_by(user_id=current_user.id).all() 
    # current_user.projects would probably do the same thing            
    return render_template('project/projects.html', projects=projects)

模板

<div class="accordion style-two gradient-bg">
    <div class="panel-group" id="accordion-2">
        {% for project in projects %}
        <div class="panel panel-default">
            <!-- Accordian Title -->
            <div class="panel-heading">
                <div class="panel-title">
                    <a class="accordion-toggle collapsed" href="#{{ project.id }}" data-parent="#accordion-2" data-toggle="collapse" aria-expanded="false">
                        {{ project }} - HELP NEEDED HERE--> ({{ project.user[0].job }})
                    </a>
                </div>
            </div>
            <!-- Accordian Detail -->
            <div id="{{ project.id }}" class="panel-collapse collapse">
                <div class="panel-body">
                    <div class="container">
                        <div class="row">
                        <p>Lorem ipsum dolor sit amet, ei vis iriure phaedrum, sea no regione delicata sadipscing. Minim imperdiet vix ad, pro ei utinam dicunt epicurei. Vide affert debitis has ex. Vel ut consul molestiae pertinacia. Duo graeci dictas consulatu in, ei veniam singulis qui, decore numquam duo id.</p>                        
                        <p>Prima tritani veritus id pro, elitr hendrerit comprehensam ei per. Doming integre aliquando has at. Ea eam aeterno lobortis, ea est mutat laudem semper. Pertinax urbanitas nec ne, quo aliquip voluptaria scriptorem ut. Ut mel tractatos reprimique contentiones, adhuc nulla apeirian usu no, mel imperdiet molestiae abhorreant ut.</p> 
                        </div>
                    </div>
                </div>
            </div>
        </div>
        {% endfor %}
   </div>
</div>

如上面所示，我有project.users[0].job，它将显示正确的信息，因为我在系统中只有一个用户。在循环遍历current_user时，如何在模板级别指定要寻找与projects关联的职务？谢谢!

Answer 1

正如注释中所建议的，最简单的方法是先查询关联的表：projects = Job.query.filter_by(user_id=current_user.id).all()

我可以随时访问每个引用的Project (project.project)，也可以获得作业的详细信息(project.job)。