python之SQLAlchemy M2M关系查询

我有以下模型，我正在尝试列出在用户组中有一个组的所有最新文档版本。

class User(Base, BaseModel): 
    __tablename__ = 'users' 
    id = Column(Integer, primary_key=True) 
    email = Column(Unicode(EMAIL_LENGTH), unique=True, nullable=False) 
    full_name = Column(Unicode(FULL_NAME_LENGTH), nullable=False) 
    deleted = Column(Boolean, nullable=False, default=False) 
 
    document_versions = relationship( 
        'DocumentVersion',  
        backref="author") 
 
 
class Group(Base, BaseModel): 
    __tablename__ = 'groups' 
    __table_args__ = (UniqueConstraint('name','company_id'),) 
    id = Column(Integer, primary_key=True) 
    name = Column(Unicode(60), nullable=False) 
    deleted = Column(Boolean, nullable=False, default=False) 
 
    # Companies have many groups 
    company_id = Column( 
            Integer, 
            ForeignKey('companies.id'), 
            nullable=False) 
    roles = relationship("Role", 
            primaryjoin="Group.id==Role.group_id", 
            backref="group") 
    users = relationship("User", 
            secondary=group_user, 
            backref="groups") 
 
document_version_group = Table( 
        "document_version_groups", 
        Base.metadata, 
        Column('group_id', Integer, ForeignKey('groups.id')), 
        Column('document_version_id', Integer, ForeignKey('document_versions.id'))) 
 
class DocumentVersion(Base, BaseModel): 
    __tablename__ = 'document_versions' 
    id = Column(Integer, primary_key=True) 
    document_id = Column( 
            Integer, 
            ForeignKey('documents.id'), 
            nullable=False) 
 
    author_id = Column( 
            Integer, 
            ForeignKey('users.id'), 
            nullable=False) 
    groups = relationship( 
            "Group", 
            secondary=document_version_group, 
            lazy='dynamic', # This is newly added just now 
            backref="document_versions") 
    status = Column( 
            Enum( 
                "draft", 
                "review", 
                "approval", 
                "declined", 
                "approved", 
                name="status"), 
            default="draft", 
            nullable=False) 

我尝试了以下但没有成功(注意返回的 document_version 为零组)

>>> docs = session.query(DocumentVersion).filter(Document.company == user.company).filter(Group.id.in_([g.id for g in user.groups])).all() 
 
2012-08-31 15:14:19,651 INFO  [sqlalchemy.engine.base.Engine][MainThread] SELECT document_versions.id AS document_versions_id, document_versions.document_id AS document_versions_document_id, document_versions.document_current_draft_id AS document_versions_document_current_draft_id, document_versions.document_current_active_id AS document_versions_document_current_active_id, document_versions.author_id AS document_versions_author_id, document_versions.status AS document_versions_status, document_versions.major_version AS document_versions_major_version, document_versions.minor_version AS document_versions_minor_version  
FROM document_versions, documents, groups  
WHERE %(param_1)s = documents.company_id AND groups.id IN (%(id_1)s) 
2012-08-31 15:14:19,651 INFO  [sqlalchemy.engine.base.Engine][MainThread] {'id_1': 1, 'param_1': 1} 
 
>>> [d.groups.all() for d in docs] 
 
2012-08-31 15:15:29,996 INFO  [sqlalchemy.engine.base.Engine][MainThread] SELECT groups.id AS groups_id, groups.name AS groups_name, groups.deleted AS groups_deleted, groups.company_id AS groups_company_id  
FROM groups, document_version_groups  
WHERE %(param_1)s = document_version_groups.document_version_id AND groups.id = document_version_groups.group_id 
2012-08-31 15:15:29,996 INFO  [sqlalchemy.engine.base.Engine][MainThread] {'param_1': 1} 
2012-08-31 15:15:29,998 INFO  [sqlalchemy.engine.base.Engine][MainThread] SELECT groups.id     AS groups_id, groups.name AS groups_name, groups.deleted AS groups_deleted, groups.company_id AS groups_company_id  
FROM groups, document_version_groups  
WHERE %(param_1)s = document_version_groups.document_version_id AND groups.id = document_version_groups.group_id 
2012-08-31 15:15:29,998 INFO  [sqlalchemy.engine.base.Engine][MainThread] {'param_1': 2} 
[[], [<project.models.Group object at 0x27ead50>]] 

请您参考如下方法:

假设此 SQL 给出正确的结果:

SELECT * 
FROM document_versions 
WHERE 
    EXISTS ( 
        SELECT 1 
        FROM documents 
        WHERE 
            document_versions.document_id = documents.id 
            AND documents.company_id = :company_id 
    ) 
    AND EXISTS ( 
        SELECT 1 
        FROM document_version_groups 
        WHERE 
            document_versions.id = document_version_groups.document_version_id 
            AND document_version_groups.group_id IN :group_ids 
    ) 

那么下面的 SQLAlchemy 表达式应该可以工作:

session.query(DocumentVersion).filter(and_( 
    DocumentVersion.document.has(compay=user.company), 
    DocumentVersion.groups.any( 
        Group.id.in_([g.id for g in user.groups]) 
    ), 
)).all() 

请注意关系属性 DocumentVersion.document 和 DocumentVersion.groups 的显式使用。没有它们，查询将只包含一堆不相关的表，从而给您误报结果。