How to put a condition on a result directly in a filter in SQLalchemy?

Question

I have a table like this:

class Continent(Base):
    __tablename__ = "continents"

    id = Column(Integer, primary_key=True, index=True)
    name = Column(String(100), unique=True)
    created_date: Column(Integer)
    season_created = Column(Integer)

I would like to be able to retrieve the list of my Continents according to the creation date. So, I will spend a year and a season (example, date: 10, and a season: 1)

I would like to get all the dates that are older than the past date. So I would do this:

db.query(Continent).filter(Continent.created_date <= date_year).all()

But, I still have to check that if the year of the result is the same as the year requested, I have to check the seasons, to make sure that the season requested, is older than the season of the data.

So my question is: How can I check with a condition DIRECTLY during my query?

Answer 1

Finally, the solution that seemed to me the most coherent was the use of case

db.query(Continent).filter(case(Continent.created_date < date_years,Continent.created_date < date_years),
                              (Continent.created_date == date_years, Continent.season_created <= season),)).all()