title: SQLAlchemy: Hybrid expression with relationship
body: <p>I have two Flask-SQLAlchemy models with a simple one-to-many relationship, like the minimal example below:</p>
  
  <pre><code>class School(db.Model):
      id = db.Column(db.Integer, primary_key=True)
      name = db.Column(db.String(30))
      address = db.Column(db.String(30))
  
  class Teacher(db.Model):
      id = db.Column(db.Integer, primary_key=True)
      name = db.Column(db.String(30))
      id_school = db.Column(db.Integer, db.ForeignKey(School.id))
  
      school = relationship('School', backref='teachers')
  </code></pre>
  
  <p>Then I add an hybrid property to teacher that uses the relationship, like so:</p>
  
  <pre><code>@hybrid_property
  def school_name(self):
      return self.school.name
  </code></pre>
  
  <p>And that property works just fine when I use it as <code>teacher_instance.school_name</code>. However, I'd also like to make queries like <code>Teacher.query.filter(Teacher.school_name == 'x')</code>, but that gives me an error:</p>
  
  <pre><code>`AttributeError: Neither 'InstrumentedAttribute' object nor 
  'Comparator' object has an attribute 'school_name'`. 
  </code></pre>
  
  <p>Following SQLAlchemy documentation, I added a simple hybrid expression, like the following:</p>
  
  <pre><code>@school_name.expression
  def school_name(cls):
      return School.name
  </code></pre>
  
  <p>However, when I try the same query again, it generates an SQL query without the join clause, so I get all available rows in School, not only those matching the foreign key in Teacher. </p>
  
  <p>From SQLAlchemy documentation I realized that the expression expects a context where the join is already present, so I tried the query again as:</p>
  
  <p><code>Teacher.query.join(School).filter(Teacher.school_name == 'x')</code></p>
  
  <p>And that actually works, but it defeats the purpose of trying to get the syntactic sugar in there in the first place if I need knowledge of the School model to get that. I expect there's a way to get that join in the expression, but I couldn't find it anywhere. The documentation has an example with the expression returning a subquery built directly with the <code>select()</code>, but even that didn't work for me.</p>
  
  <p>Any ideas?</p>
  
  <p><strong>UPDATE</strong></p>
  
  <p>After Eevee's answer below, I used the association proxy as suggested and it works, but I also got curious with the comment that it should work with the <code>select()</code> subquery and tried to figure out what I did wrong. My original attempt was:</p>
  
  <pre><code>@school_name.expression
  def school_name(cls):
      return select(School.name).where(cls.id_school == School.id).as_scalar()
  </code></pre>
  
  <p>And it turns out that was giving me an error because I had missed the list in select(). The code below works fine:</p>
  
  <pre><code>@school_name.expression
  def school_name(cls):
      return select([School.name]).where(cls.id_school == School.id).as_scalar()
  </code></pre>