how to query a flask sqlalcheme tha has two relationship from another single table

Question

I am trying to query a table that contains userId, which references users, and taggedNameId, which also references users and view it in my template. But i keep getting this error "int object' has no attribute 'users'"

below is my try

This is my Model

class Users(db.Model):
     id = db.Column(db.Integer, primary_key=True)
     fullname = db.Column(db.String(100))
     username = db.Column(db.String(100), unique=True)
     email = db.Column(db.String(100), unique=True)
     gender = db.Column(db.String(100))
     phonenumber = db.Column(db.Integer)
     role = db.Column(db.String(100))
     Password = db.Column(db.String(100))
     datecreated = db.Column(db.DateTime, default=datetime.now)
     notif_user = db.relationship('notifications', foreign_keys="notifications.userId", backref='users')
     tagged_user = db.relationship('notifications', foreign_keys="notifications.taggednameId")


 def __init__(self):
     return self

class notifications(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    userId = db.Column(db.Integer, db.ForeignKey('users.id'))
    content = db.Column(db.String(100))
    taggednameId = db.Column(db.Integer, db.ForeignKey('users.id'), nullable=True)
    datecreated= db.Column(db.DateTime, default=datetime.now)


def __init__(self):
   return self

This is my Flask-SQLAlchemy Query

notifications=table.notifications.query.filter(table.notifications.userId==table.Users.id).order_by(table.notifications.datecreated.desc()).all()

HTML: When I run the code, it says 'int object' has no attribute 'users'. I expect to get the full name of the user who initiated the notification and the full name of the user tagged

 <table class="table table-hover align-middle">
     <thead>
         <tr>
             <th class="fw-bold">SN</th>
             <th class="fw-bold">Name</th>
             <th class="fw-bold">Notifcation_Content</th>
             <th class="fw-bold">Tagged name</th>
             <th class="fw-bold">Date Posted</th>
         </tr>
     </thead>
     <tbody class="table-group-divider">
         {% for N in admindata.notifications %}
         <tr>
             <td>{{loop.index}}</td>
             <td>{{N.users.fullname}}</td>
             <td>{{N.content}}</td>
             <td>{{N.taggednameId.users.fullname}}</td>
             <td>{{N.datecreated}}</td>
         </tr>
         {% endfor %}
     </tbody>
 </table>

Answer 1

To retrieve the full name of the tagged user, you use the ForeignKey and not the possible backreference of the relationship. However, the ForeignKey is an integer that doesn't have the users attribute. Furthermore, the backreference for the tagged_user attribute is not set and therefore doesn't exist.

The following simplified example shows what an implementation without using the "Legacy Query Interface" might look like.

from datetime import datetime
from flask import (
    Flask, 
    render_template_string
)
from flask_sqlalchemy import SQLAlchemy
from sqlalchemy.sql import func 
from sqlalchemy.orm import DeclarativeBase 
from typing import List

class Base(DeclarativeBase):
    pass

app = Flask(__name__)
app.config.from_mapping(
    SECRET_KEY='your secret here', 
    SQLALCHEMY_DATABASE_URI='sqlite:///sample.db', 
)

db = SQLAlchemy(app, model_class=Base)

class User(db.Model):
    id: db.Mapped[int] = db.mapped_column(db.Integer, primary_key=True)
    fullname: db.Mapped[str] = db.mapped_column(db.String(100), nullable=False)
    
    # ...

    notifications_sent: db.Mapped[List['Notification']] = db.relationship(
        foreign_keys='Notification.sender_id', 
        back_populates='sender'
    )
    notifications_recieved: db.Mapped[List['Notification']] = db.relationship(
        foreign_keys='Notification.recipient_id', 
        back_populates='recipient'
    )

class Notification(db.Model):
    id: db.Mapped[int] = db.mapped_column(db.Integer, primary_key=True)
    content: db.Mapped[str] = db.mapped_column(db.Text)
    date_created: db.Mapped[datetime] = db.mapped_column(
        db.DateTime(timezone=True), 
        nullable=False, 
        server_default=func.now()
    )
    sender_id: db.Mapped[int] = db.mapped_column(
        db.ForeignKey('user.id'), 
        nullable=False
    )
    sender: db.Mapped['User'] = db.relationship(
        foreign_keys='Notification.sender_id', 
        back_populates='notifications_sent'
    )
    recipient_id: db.Mapped[int] = db.mapped_column(
        db.ForeignKey('user.id'), 
        nullable=False
    )
    recipient: db.Mapped['User'] = db.relationship(
        foreign_keys='Notification.recipient_id', 
        back_populates='notifications_recieved'
    )

with app.app_context():
    db.drop_all()
    db.create_all()

    user1 = User(fullname='Bruce Wayne')
    user2 = User(fullname='Clark Kent')
    db.session.add_all([user1, user2])
    db.session.commit()

    notification1 = Notification(
        sender=user1, 
        recipient=user2, 
        content='Do you bleed?'
    )
    db.session.add(notification1)
    db.session.commit()

@app.route('/')
def index():
    notifications = db.session.scalars(
        db.select(Notification).order_by(Notification.date_created.desc())
    ).all()
    return render_template_string(
        '''
            <table>
                <thead>
                    <tr>
                        <th>Sender</th>
                        <th>Recipient</th>
                        <th>Content</th>
                        <th>Date Posted</th>
                    </tr>
                </thead>
                <tbody>
                    {% for n in notifications -%}
                    <tr>
                        <td>{{ n.sender.fullname }}</td>
                        <td>{{ n.recipient.fullname }}</td>
                        <td>{{ n.content }}</td>
                        <td>{{ n.date_created }}</td>
                    </tr>
                    {% endfor -%}
                </tbody>
            </table>
        ''', 
        **locals()
    )