Multiple join in SQLalchemy

Question

I've got some objects that look like below. I've got some property units, which contain one or more tenants, and the tenant object has a one-to-one relationship with users

class User(Base):
    """
    Application's user model.
    """
    __tablename__ = 'usr_users'
    usr_user_id = Column(Integer, primary_key=True)
    usr_email = Column(Unicode(50))
    _usr_password = Column('password', Unicode(64))
    usr_groups = Column(Unicode(256))
    usr_activated = Column(Boolean)

    tenant = relationship("Tenant", uselist=False, backref="usr_users")
class Tenant(Base):
    __tablename__ = 'ten_tenants'
    ten_tenant_id = Column(Integer, primary_key=True)
    ten_ptu_property_unit_id = Column(Integer, ForeignKey('ptu_property_units.ptu_property_unit_id'))
    ten_usr_user_id = Column(Integer, ForeignKey('usr_users.usr_user_id'))

class PropertyUnit(Base):
    __tablename__ = 'ptu_property_units'
    ptu_property_unit_id = Column(Integer, primary_key=True)
    ptu_pty_property_id = Column(Integer, ForeignKey('pty_propertys.pty_property_id'))

    tenants = relationship("Tenant")

I'm attempting to pull all of the units for a property, including the tenant information and the email from the user table.

I managed to get one join pretty easy:

rows = DBSession.query(PropertyUnit).join(Tenant).filter(PropertyUnit.ptu_pty_property_id==request.GET['property_id']).order_by(PropertyUnit.ptu_number)
    units = rows.all()

And I'm displaying in the template like this:

% for unit in units:
      <%
          tenants = unit.tenants
      %>
        <tr>
        <td><a href="/manager/unit?property_unit_id=${unit.ptu_number}">${unit.ptu_number}</a></td>
        <td>
        % for tenant in tenants:
            ${tenant.ten_usr_user_id},
        % endfor
        </td>
        </tr>
      % endfor

So far so good. Now I need to pull the user information from the tenant foreign key, so I thought I could just tack on another join:

rows = DBSession.query(PropertyUnit).join(Tenant).join(User).filter(PropertyUnit.ptu_pty_property_id==request.GET['property_id']).order_by(PropertyUnit.ptu_number)
    units = rows.all()

This appears to work in the SQL logs, as it generates the right SQL, but I'm unable to get to the data in the same way that I did the first time. This fails:

% for unit in units:
      <%
          tenants = unit.tenants
      %>
        <tr>
        <td><a href="/manager/unit?property_unit_id=${unit.ptu_number}">${unit.ptu_number}</a></td>
        <td>
        % for tenant in tenants:
            <%
                user = tenant.User
            %>
            ${tenant.ten_usr_user_id},
        % endfor
        </td>
        </tr>
      % endfor

So, the above code throws a "'Tenant' object has no attribute 'User'" error.

How do I get to that user join?

Answer 1

There's no attribute User on Tenant because you didn't define one. You called it usr_users in the backref, so you should access it as tenant.usr_users.