SQLAlchemy relationship selection criteria

Question

I have two model classes:

class Programs(db.Model):
    __tablename__ = "programs"
    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String(100), nullable=False)
    description = db.Column(db.String(100), nullable=False)
    duration = db.Column(db.Integer, nullable=False)
    date_created = db.Column(db.DATE, default=datetime.now())
    created_by = db.Column(db.String(100))
    program_sessions = db.relationship('Program_Session',backref='programs')
   
class Program_Session(db.Model):
    __tablename__ = "program_session"
    id = db.Column(db.Integer, primary_key=True)
    session_title = db.Column(db.String(100), nullable=False)
    session_description = db.Column(db.String(100))
    session_year = db.Column(db.Integer)
    program_id = db.Column(db.Integer, db.ForeignKey("programs.id"), nullable=False)
    students = db.relationship('Student_Registration', backref='program_session')
    date_created = db.Column(db.DATE, default=datetime.now())
    created_by = db.Column(db.String(100))

I create an object of Programs with:

program = Programs.query.first()

Now I can access all the Program_Sessions from the selected Program:

print(pro.program_sessions)

Is it possible to subquery/query to retrieve only those Program_session in Program whose year is 2021?

Answer 1

Option-1: filter on 'python' (in memory)

Once you get all Program_Sessions (all_sessions = pro.program_sessions), you filter them by sessions_2021 = [item for item in all_sessions if item.session_year == 2021].

Needless to say, this is not efficient at all as lots of data will be loaded from the database to be immediately discarded.

Option2: use Dynamic Relationship Loaders

Define the relationship with lazy="dynamic", which will return a Query and hence you will be able to apply additional criteria to the query:

class Programs(db.Model):
    # ...
    program_sessions = db.relationship('Program_Session', backref='programs', lazy="dynamic")

program = Programs.query.first()
sessions_2021 = program.program_sessions.filter(Program_Session.year == 2021).all()

Option3: use orm.with_parent [BEST]

sessions_2021 = select(Program_Session).where(with_parent(program, Program_Session.programs)).where(Program_Session.year == 2021)

Answer 2

The answer is yes...

van's answer shows you options for playing with sqlalchemy's query mechanism. But what if you want to write this logic on the Programs class itself? That way anywhere you have a Programs object, you can access the filter.

You can do it in pretty plain python by altering the Programs class like so:

class Programs(db.Model):
    __tablename__ = "programs"
    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String(100), nullable=False)
    description = db.Column(db.String(100), nullable=False)
    duration = db.Column(db.Integer, nullable=False)
    date_created = db.Column(db.DATE, default=datetime.now())
    created_by = db.Column(db.String(100))
    program_sessions = db.relationship('Program_Session',backref='programs')
    
    '''Here I add a filter that returns only the sessions for a particular year
    '''
    def program_sessions_by_year(self, year):
        return filter(lambda ps: ps.session_year == year, self.program_sessions)

If you care about efficiency, you can get the database to do the filtering for you using a bit more sqlalchemy magic:

from sqlalchemy.orm import object_session

class Programs(db.Model):
    __tablename__ = "programs"
    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String(100), nullable=False)
    description = db.Column(db.String(100), nullable=False)
    duration = db.Column(db.Integer, nullable=False)
    date_created = db.Column(db.DATE, default=datetime.now())
    created_by = db.Column(db.String(100))
    program_sessions = db.relationship('Program_Session',backref='programs')
    
    '''Improve efficiency by using DB's SQL engine to filter the object.
    '''
    def program_sessions_by_year(self, year):
        return object_session(self)\
                 .query(Program_Session)\
                     .filter_by(session_year=year, program_id=self.id)\
                         .all()
           

Either way you can then write (where-ever you have a Program object):

# lets say you just want the first program
first_program = Programs.query.first()
# to get the program sessions by year 2021
first_program.program_sessions_by_year(2021)

There's probably a bunch of other ways you could do something like this. SqlAlchemy is a big library. For more background on my answer, have a look at the SQL expressions as Mapped Attributes docs.