One-to-One Relationship in Flask-SQLAlchemy Only Working One Way

Question

I have two models within a flask application, they form a one-to-one relationship, where the 'Staff' model can be associated with one 'User'. There can be 'Staff' without them being a 'User', but not a 'User' without being 'Staff'.

Unfortunately, I'm falling at the first hurdle. I'm trying to combine the models in a HTML table, templated through Jinja2. The table is supposed to list all 'Staff' and identify whether they are a 'User'. If they are a 'User' it is supposed to identify whether or not they are active.

Here are my models as they stand:

class Staff(db.Model):
    __tablename__ = 'staff'
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(80), nullable=False)
    job_title = db.Column(db.String(255), nullable=True)
    is_user = db.Column(db.Boolean, nullable=False, default=False)
    user = db.relationship('User', 
        backref=db.backref('staff', lazy=True))

    def __repr__(self):
        return f'Staff Member: {self.id}, {self.name}, {self.job_title}'


class User(db.Model, UserMixin):
    __tablename__ = 'user'
    id = db.Column(db.Integer, primary_key=True)
    email = db.Column(db.String(120), unique=True, nullable=False)
    image_file = db.Column(db.String(20), nullable=False, 
        default='default.jpg')
    password = db.Column(db.String(255), nullable=False)
    active = db.Column(db.Boolean, nullable=False, default=False)

    staff_id = db.Column(db.Integer, db.ForeignKey('staff.id'), 
       nullable=False)

    def __repr__(self):
        return f'User({self.name}, {self.email}, {self.image_file})'

And here is an example of the relationship in Jinja2. This code segment works as expected:

{% for user in users %}
<tr>
    <td>{{ user.staff.name }}</td>
    <td>{{ user.staff.job_title }}</td>
    <td>{{ user.email }}</td>
    {% if user.active == True %}
    <td>Yes</td>
    {% else %}
    <td>No</td>
    {% endif %}
{% endfor %}

This code, however, where I'm trying to get 'User' details while looping through all 'Staff' doesn't work at all:

{% for staff in all_staff %}
    <tr>
        <td>{{ staff.name }}</td>
        <td>{{ staff.job_title }}</td>
    {% if staff.is_user == True %}
        {% if staff.user.active == True %}
        <td class="text-success">Yes (Active)</td>
        {% elif staff.user.active == False %}
        <td class="text-warning">Yes (Inactive)</td>
        {% endif %}
    {% elif staff.is_user == False %}
        <td class="text-danger">No</td>
    {% endif %}
{% endfor %}

While the relationship works one way it doesn't work in the second table example; it returns no HTML code at all unless the staff.is_user == False statement is true.

I've tried to create a new db.relationship in the 'User' class. I've also tried a db.relationship in both classes at once. I've tried the Jinja2 if statements on the same line, as well:

{% if staff.is_user == True and staff.user.active == True %}

None of these are working however, and where I'd expect the correct html to be returned based on the results of the if statements, I'm getting no results at all when referring to the 'Users' table, via the 'Staff table.

I'm sure I'm missing something simple but I can't figure it out.

Can anybody see where I'm going wrong?

There is no staff = db.relationship("Staff") in class User so are user.staff references ever going to work? — J.J. Hakala
Hi @J.J.Hakala I've tried to put that relationship in the class User at the same time, and tried both relationships individually, but nothing has worked, no matter what I call the relationship or the backref. Any ideas? — patrick.mchugh4

patrick.mchugh4 patrick.mchugh4 · Accepted Answer · 2019-01-27T22:21:24

It turns out the answer was hiding in the 'One-to-May Relationships' section in the Flask-SQLAlchemy docs. In order to reference a one-to-one relationship, you need to set uselist=False in the relationship. Exactly as it sounds, it loads the connection as a scalar rather than a list.

I'd also made an error in taking some information from the main SQLAlchemy docs and not correctly loading the backref in the relationship. In Flask-SQLAlchemy, it simply needs to be declared as a string. In this case backref='staff'.

This code works exactly as expected:

class Staff(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(80), unique=True, nullable=False)
    job_title = db.Column(db.String(80))
    is_user = db.Column(db.Boolean, default=False)
    user = db.relationship('User', backref='staff', lazy=True, uselist=False)

    def __repr__(self):
        return f'Staff Member: {self.id}, {self.name}, {self.job_title}'


class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    email = db.Column(db.String(120), unique=True, nullable=False)
    active = db.Column(db.Boolean, nullable=False, default=False)
    staff_id = db.Column(db.Integer, db.ForeignKey('staff.id'), nullable=False)

    def __repr__(self):
        return f'User Account: {self.id}, {self.email}'

One-to-One Relationship in Flask-SQLAlchemy Only Working One Way

1 Answers