Flask + SqlAlchemy retrieve a class object from another class attribute

Question

I'm building a flask application using SqlAlchemy and I'd like to retrieve the author from a Notification object from the Notification object itself, rather then parsing all Users to a template in order to capture the author's data.

Here's my models:

In app/models.py

from app import db
from datetime import datetime

#...

class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(250), unique=True, index=True)
    password_hash = db.Column(db.Binary(72))
    notifications = db.relationship('Notification', backref='recipient', lazy='dynamic')

#...

class Notification(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    body = db.Column(db.String(140))
    timestamp = db.Column(db.DateTime, index=True, default=datetime.utcnow)
    user_id = db.Column(db.Integer, db.ForeignKey('user.id'))
    pending = db.Column(db.Boolean, default=True)
    author = ?

In my routes, I always parse the notifications from a current_user as an argument, as I use it in my templates/base.html navbar (and all my views {% extends "base.html %})

Here's one route example:

In app/routes.py

@app.route('/')
@app.route('/index')
def index():
        notifications = User.query.filter_by(id=current_user.id).first().notifications.all()
    else:
        notifications = []
    return(render_template('index.html', title=_('Home'), notifications=notifications))

But, I don't want to parse all the Users from the database in order to get the author information from each notification, instead I want to access the author User object in my template from the Notification object itself, like so:

In app/templates/index.html

{{ notification.author.username }}

And that should return its username.

What kind of relationship or trick would I have to do to achieve that?

P.S: Would that be in any way insecure as a user (a notification recipient) could somehow exploit it to access unwanted data from the author, such as nothification.author.password_hash for example? If so, what would be the best way to implement what I'm trying to?

Edit

To clarify the question, as pointed by @noslenkwah, I'd like to make it clear that the author is a User and I hope that I can create a Notification object by assigning things as follows:

notification = Notification(body='example', user_id=some_recipient_id, author = User.query.filter_by(username=some_author_username).first())

I could then db.session.add(notification) and finally db.session.commit() it.

some_recipient_id is the id of a User, being an unique identifier and its primary key and some_author_username is also a unique identifier. I could also filter_by(id=some_author_id), whereas some_author_id would be a User's primary key and also a unique identifier.

What is the author? Is it a `User`? How is the author determined? Can it be easily defined when a `Notification` object is created? — noslenkwah, Nov 11 '19 at 19:52
Hi @noslenkwah, thanks for asking, I'll be updating the question. The author is a User and I'm hoping I can create a Notification object setting the author as an User, either from the flask shell, by running `not = Notification(body='something',user_id=some_integer,author=User.query.filter_by(id=some_integer).first())`, then I'll be running `db.session.add(not)` and `db.session.commit()`, alternatively I'll be creating a `route` to create a `Notification` when running the app, with its own `template`, containing fields fill all the required attributes. — EduGord, Nov 12 '19 at 00:54
You can do that. Your answer can be found in [this](https://stackoverflow.com/questions/7548033/how-to-define-two-relationships-to-the-same-table-in-sqlalchemy) question — noslenkwah, Nov 12 '19 at 14:15
@noslenkwah , thank you very much for pointing to that question. I've managed to solve my problem reading the solutions! — EduGord, Nov 14 '19 at 01:47

EduGord · Accepted Answer · 2019-11-14T02:01:44.040

Thanks to @noslenkwah pointing to this question, I was able to find a way to accomplish what I was trying to.

Here's how:

in models.py

from app import db
from sqlalchemy.orm import backref
from datetime import datetime

#...

class Notification(db.Model):
    __tablename__ = 'notification'

    id = db.Column(db.Integer, primary_key=True)
    body = db.Column(db.String(140))
    timestamp = db.Column(db.DateTime, index=True, default=datetime.utcnow)
    pending = db.Column(db.Boolean, default=True)

    user_id = db.Column(db.Integer, db.ForeignKey('user.id'))
    author_id = db.Column(db.Integer, db.ForeignKey('user.id'))

    recipient = db.relationship(User, foreign_keys=[user_id],
                                      backref=backref('notifications', order_by=id, lazy='dynamic'))
    author = db.relationship(User, foreign_keys=[author_id])

Flask + SqlAlchemy retrieve a class object from another class attribute

Edit

1 Answers1