title: Nested Association Proxy with SQLAlchemy
body: <p>I'm trying to create a databases where :
  A user can have multiple companies
  A user can only have one role at a given company</p>
  <p>So I have made these tables :</p>
  <pre><code>class User(Base):
      __tablename__ = &quot;users&quot;
      user_id = Column(Integer, primary_key=True, index=True)
      name = Column(String)
  
      companies = association_proxy(
          'user_companies',
          'role',
          creator=lambda company, role: UserRoleAtCompany(company=company, role=role)
      )
  
  class Company(Base):
      __tablename__ = &quot;company&quot;
      company_id = Column(Integer, primary_key=True, index=True)
      name = Column(String, index=True)
  
  class UserRole(Base):
      __tablename__ = &quot;user_role&quot;
      user_role_id = Column(Integer, primary_key=True, index=True)
      name = Column(String)
  
  class UserRoleAtCompany(Base):
      __tablename__ = &quot;user_role_at_company&quot;
      user_role_id = Column(Integer, ForeignKey(&quot;user_role.user_role_id&quot;))
      company_id = Column(Integer, ForeignKey(&quot;company.company_id&quot;), primary_key=True)
      user_id = Column(Integer, ForeignKey(&quot;users.user_id&quot;), primary_key=True)
  
      user = relationship(
          User,
          backref=backref(&quot;user_companies&quot;,
          collection_class=attribute_mapped_collection(&quot;company_role&quot;))
      )
  
      company = relationship(&quot;Company&quot;)
      company_role = association_proxy('company', &quot;company_role&quot;)
  
      role = relationship(&quot;UserRole&quot;)
  </code></pre>
  <p>When I query a user I can see which companies he has with <code>user.companies</code> and it returns something like <code>{(1, 'Company Name'): UserRole(role_name)}</code>.</p>
  <p>But What I would like to have is when I query <code>user.companies</code>, it returns a list of company objects and appends to each company object the role attribute he has on that particular company, so I can access the role of an user like this <code>user.companies[0].role</code></p>
  <p>I don't know if this is possible or if I miss something, so I would like your opinions and ideas.</p>
  <p>Thanks in advance.</p>