Filter on SQLAlchemy relationship

Question

I am using Pyramid + SQLAlchemy and have the following set up

from sqlalchemy import orm
from __init__ import user_table  
from device import Device

class User(object):
  def __repr__(self):
    return 'User: ' + self.username

orm.mapper(User, user_table, properties={
  'devices': orm.relation(Device, backref='owner')
})

How can I add a filter when I access user.devices? When I try user.devices.filter(Device.platform==1).all(), I get the following error:

'InstrumentedList' object has no attribute 'filter'

Answer 1

You can either use a Query-Enabled Property:

class User(object):
    def __repr__(self):
        return 'User: ' + self.username

    # property
    @property
    def devices_filtered(self):
        return object_session(self).query(Device).with_parent(self).filter(Device.platform==1).all()

    # or simply a method
    def devices_filtered(self, platform):
        return object_session(self).query(Device).with_parent(self).filter(Device.platform==platform).all()

or see the Dynamic Relationship Loaders:

orm.mapper(User, user_table, properties={
  'devices': orm.relation(Device, backref='owner', lazy='dynamic')
})

# ...
user = session.query(User).get(_some_user_id)
devices = user.devices.filter(Device.platform==platform).all()

Just read the referenced documentation for the full explanation on the topics.