如何在两个表上执行基本连接？

在学习 SQL Alchemy 时，遇到一个看似简单但难以解决的问题：如何连接两个表。数据库中有四个表：User、Contact、ContactGroups 和 ContactGroupUsers，它们通过外键关联。当查询特定组中的联系人时，需要检索所有 filter(ContactGroupUsers.group_id == group_id).all() 的行，然后将每个 ContactGroupUsers.user_id 连接到该用户在 Contact 表中的条目。因此，最终想要的结果是：ContactGroupUsers.group_id 以及对应用户在联系人表中的信息。

2、解决方案
解决方案是使用关联代理定义关系。关联代理允许在一个表中定义另一个表的外键列。在 User 表中，可以为 contacts 定义一个关联代理，使得可以像访问 User 表中的列一样访问 Contact 表中的列。类似地，可以在 Contact 表中为 contact 定义一个关联代理，以便像访问 Contact 表中的列一样访问 User 表中的列。

以下是如何使用关联代理定义关系的示例代码：

from sqlalchemy.ext.associationproxy import association_proxy

class User( Base ):

    __tablename__       = 'user'

    id                  = Column( Integer, primary_key=True )

    contacts            = relationship( 'Contact',
        primaryjoin = 'foreign( User.id )=remote( Contact.user_id )',
        backref     = 'user'
    )
    contact_groups      = relationship( 'ContactGroup', backref='user' )
    contact_group_users = relationship( 'ContactGroupUsers', backref='user' )

    contact_users = association_proxy( 'contacts', 'contact' )

class Contact( Base ):

    __tablename__       = 'contact'

    id                  = Column( Integer, primary_key=True )

    user_id             = Column( Integer, ForeignKey( 'user.id' ) )
    contact_id          = Column( Integer, ForeignKey( 'user.id' ) )

    contact             = relationship( 'User',
        primaryjoin = 'foreign( Contact.contact_id ) = remote( User.id )',
        backref     = 'user_contacts'
    )

class ContactGroup( Base ):

    __tablename__       = 'contact_group'

    id                  = Column( Integer, primary_key=True )

    user_id             = Column( Integer, ForeignKey( 'user.id' ) )

class ContactGroupUser( Base ):

    __tablename__       = 'contact_group_user'

    id                  = Column( Integer, primary_key=True )

    group_id            = Column( Integer, ForeignKey( 'contact_group.id' ) )
    user_id             = Column( Integer, ForeignKey( 'user.id' ) )

    contact_group       = relationship( 'ContactGroup' )

使用关联代理后，就可以像访问一个表中的列一样访问另一个表中的列。例如，要查询所有属于某个组的联系人，可以使用以下代码：

original_user = aliased( User )

contacts = db.session.query( User )\
    .join( User.user_contacts )\
    .join( original_user, original_user.id == Contact.user_id )\
    .join( User.contact_group_users )\
    .join( ContactGroupUser.contact_groups ).filter(
        and_(
             original_user.id == user_id,
             contact_group.user_id == original_user.id,
             contact_group.group_id == group_id
        )
     ).all()

这段代码首先创建了一个 User 表的别名 original_user。然后，它连接了 User 表和 Contact 表，以及 User 表和 ContactGroupUsers 表。最后，它过滤掉了不属于指定组的联系人。