Get last actual data

There is a table rule_chains:

My problem is to get the actual entity for today. For example today is 2023-03-01 and I need to get this two entity with id = 1 and id = 2. But if today is 2023-04-01 I need to get id = 2 and id = 3. Because actual rule_chain for today by payroll_type_id = 1 is entity, which id = 3.

I tried to write code with sqlalchemy, like this, but it doesn’t work.

subquery = self.session.query(func.min(RuleChain.start_date).label("start_date"),
                                          RuleChain.stuff_department_id,
                                           RuleChain.payroll_type_id).group_by(
     RuleChain.stuff_department_id, RuleChain.payroll_type_id).having(
     func.max(RuleChain.start_date) >= self.date).subquery()

 rule_chains: List[RuleChain] = self.session.query(RuleChain).filter(
     subquery.c.start_date == RuleChain.start_date,
     subquery.c.stuff_department_id == RuleChain.stuff_department_id,
     subquery.c.payroll_type_id == RuleChain.payroll_type_id
 ).all()

Maybe if you know any raw sql or sqlalchemy query, will be nice.

>Solution :

try this:

from sqlalchemy import func, and_

# Create a subquery to find the maximum start_date for each group
subquery = (
    self.session.query(
        RuleChain.stuff_department_id,
        RuleChain.payroll_type_id,
        func.max(RuleChain.start_date).label("max_start_date"),
    )
    .filter(RuleChain.start_date <= self.date)
    .group_by(RuleChain.stuff_department_id, RuleChain.payroll_type_id)
    .subquery()
)

# Join the subquery with the main table to get the actual entities for today
rule_chains: List[RuleChain] = (
    self.session.query(RuleChain)
    .join(
        subquery,
        and_(
            RuleChain.stuff_department_id == subquery.c.stuff_department_id,
            RuleChain.payroll_type_id == subquery.c.payroll_type_id,
            RuleChain.start_date == subquery.c.max_start_date,
        ),
    )
    .all()
)

Hope it works …