sqlalchemy relations and query on relations

Question

Suppose I have 3 tables in sqlalchemy. Users, Roles and UserRoles defined in declarative way. How would one suggest on doing something like this:

user = Users.query.get(1) # get user with id = 1
user_roles = user.roles.query.limit(10).all()

Currently, if I want to get the user roles I have to query any of the 3 tables and perform the joins in order to get the expected results. Calling directly user.roles brings a list of items that I cannot filter or limit so it's not very helpful. Joining things is not very helpful either since I'm trying to make a rest interface with requests such as: localhost/users/1/roles so just by that query I need to be able to do Users.query.get(1).roles.limit(10) etc etc which really should 'smart up' my rest interface without too much bloated code and if conditionals and without having to know which table to join on what. Users model already has the roles as a relationship property so why can't I simply query on a relationship property like I do with normal models?

Answer 1

Simply use Dynamic Relationship Loaders. Code below verbatim from the documentation linked to above:

class User(Base):
    __tablename__ = 'user'
    
    posts = relationship(Post, lazy="dynamic")
    
jack = session.query(User).get(id)
    
# filter Jack's blog posts
posts = jack.posts.filter(Post.headline=='this is a post')
  
# apply array slices
posts = jack.posts[5:20]