python - What is the most idiomatic way to express this query with SQAlchemy? -

Basically I have a SQLite query that looks like this:

  foos = Foo .query .filter (foo.expiration & lt; cutoff) valid_foos = [] for less important things for foo: last_bar = foo.bars.order_by ('created_at desc') first () last_bar.state if = 'fatal': Valid_foos.append (.! Foo)   
 The goal is to select all the fees for which the state of the related bar is not "fatal" It seems that the subqueries can help here However, I am struggling to understand this B i  last_bar = foo.bars.order_by ( 'created_at desc') How can I express. First ()  this way.  
  
 
 
 I think the easiest and most versatile way to do this is using extensions. When you expand the model according to the below given below:  
  square fu (base): __tablename__ = "foo" # ... @hybrid_propertydef last_bar_state (self): _last_bar = self.bars .order_by (Bar.created_at.desc ()) First. () Return _last_bar.state @last_bar_state.expressiondef_last_bar_state_expression (CLS): q = (selection ([Bar.state.label ("state")]) .where (Bar.foo_id == cls.foo_id) .order_by (bar .created_at.desc ()) .limit (1)) .label ("last_bar_state_sub") return q   
 You will be able to use  last_bar_state  in both memory In addition, in the query:  
  less important things = session.query (foo) .filter (Foo.expiration & LT; cutoff) Less Important Things = Less Important Things .filter (foo.last_bar_state ! = 'Fatal'). All ()