primaryjoin - filtrando por atributos de asociación con SqlAlchemy association_proxy

sqlalchemy relation join

Question

Tengo una relación muchos-a-muchos (desarrolladores y proyectos) modelada usando la asociación_proxy de SA. Las colecciones (desarrolladores en cada proyecto y proyectos para cada desarrollador) funcionan bien, pero necesito filtrar un atributo de la asociación en sí (estado). Algo como esto (que no funciona):

activeDevelopers = s.query(Developer).filter_by(Developer.developerProjects.status == ''active'').all()

¿Qué me estoy perdiendo? Aquí está el código de prueba completo:

import logging
from sqlalchemy import create_engine, Table, Column, Integer, String, MetaData, ForeignKey
from sqlalchemy.orm import relation, mapper, sessionmaker
from sqlalchemy.sql import *
from sqlalchemy.ext.associationproxy import association_proxy

log = logging.getLogger(''nm_test'')
logging.basicConfig(level=logging.DEBUG,
        format=''%(asctime)s,%(msecs)03d %(levelname)s [%(filename)s.%(funcName)s @ %(lineno)d.%(thread)d] %(message)s'')

engine = create_engine(''sqlite:///:memory:'', echo = False, echo_pool = False)
meta = MetaData()
meta.bind = engine

developer_table = Table(''developer'', meta,
    Column(''id'', Integer, primary_key=True, autoincrement = False),
    Column(''name'', String),
)

project_table = Table(''project'', meta,
    Column(''id'', Integer, primary_key=True, autoincrement = True),
    Column(''name'', String)
)

developer_project_table = Table(''developer_project'', meta,
    Column(''developer_id'', Integer, ForeignKey(''developer.id''), primary_key = True),
    Column(''project_id'', Integer, ForeignKey(''project.id''), primary_key = True),
    Column(''status'', String)
)

class Developer(object):
    projects = association_proxy(''developerProjects'', ''projects'')
    def __str__(self):
        return ''Developer id:%i, name:%s'' % (self.id, self.name)

class Project(object):
    developers = association_proxy(''developerProjects'', ''developers'')
    def __str__(self):
        return ''Project id:%i, name:%s'' % (self.id, self.name)

class DeveloperProject(object):
    def __str__(self):
        return ''DeveloperProject developer:%s, project:%s, status:%s'' % (self.developer_id, self.project_id, self.status)

mapper(Developer, developer_table, properties = {
    ''developerProjects'':relation(DeveloperProject, backref = "developers")
})

mapper(Project, project_table, properties = {
    ''developerProjects'':relation(DeveloperProject, backref = "projects")
})

mapper(DeveloperProject, developer_project_table)

meta.create_all(engine)
conn = engine.connect()

conn.execute(project_table.insert(),[
    {''name'':''stackoverflow''},
    {''name'':''superuser''},
])
conn.execute(developer_table.insert(),[
    {''name'':''John''},
    {''name'': ''TerryJ''},
    {''name'': ''TerryG''},
    {''name'': ''Eric''},
    {''name'': ''Graham''},
])
conn.execute(developer_project_table.insert(),[
    {''developer_id'':1, ''project_id'':1, ''status'':''active''},
    {''developer_id'':2, ''project_id'':2, ''status'':''inactive''},
    {''developer_id'':3, ''project_id'':2, ''status'':''active''},
    {''developer_id'':4, ''project_id'':1, ''status'':''active''},
    {''developer_id'':4, ''project_id'':2, ''status'':''active''},
    {''developer_id'':5, ''project_id'':1, ''status'':''active''},
    {''developer_id'':5, ''project_id'':2, ''status'':''inactive''},
])

Session = sessionmaker(bind=engine)
s = Session()

developers = s.query(Developer).all()
projects = s.query(Project).all()

for d in developers:
    log.debug(d)
    for p in d.projects:
        log.debug(''    %s'' % p)

for p in projects:
    log.debug(p)
    for d in p.developers:
        log.debug(''    %s'' % d)

# does not work
activeDevelopers = s.query(Developer).filter_by(Developer.developerProjects.status == ''active'').all()
# AttributeError: Neither ''InstrumentedAttribute'' object nor ''Comparator'' object has an attribute ''status''

Answer 1

Utilice any() método de proxy de asociación any() :

s.query(Developer).filter(Developer.developerProjects.any(status=''active''))

Answer 2

Para agregar a la respuesta de Denis:

Cuando probé el método any() , obtuve este error

sqlalchemy.exc.InvalidRequestError: ''any()'' not implemented for scalar attributes. Use has().

Usar has() vez de any() funcionó para mí.

Parece que any() es para relaciones de lista (en este caso, los desarrolladores tienen potencialmente varios proyectos, por lo que Developer.developerProjects es una lista). Considerando que has() es para una relación única (por ejemplo, si un Developer tiene un puesto de Workplace asociado).

Aquí está el enlace a la documentación: http://docs.sqlalchemy.org/en/latest/orm/internals.html#sqlalchemy.orm.properties.RelationshipProperty.Comparator.has