Querying association table object directly

Question

I have to query the relationships directly, since I have to display them seperatly.

task_user = db.Table(
    'pimpy_task_user',
    db.Column('task_id', db.Integer, db.ForeignKey('pimpy_task.id')),
    db.Column('user_id', db.Integer, db.ForeignKey('user.id'))
)

How do I do this in SQLAlchemy? When I try this:

tasks_rel = task_user.join(Task).join(User).filter(Task.group_id == group_id)

It causes this error:

AttributeError: 'Join' object has no attribute 'filter'

Answer 1

the usage you have above is creating a join() construct, which is a Core (non-ORM) construct representing a join() of two tables (but not a full blown select()).

when using the ORM you usually start off a SELECT using the Query object. Querying from the class itself is a pattern offered by extensions like flask-sqlalchemy, but these extensions are usually confusing in this regard. Given any class or table you can query against it using the Query object:

session.query(task_user).join(Task).join(User).filter(Task.group_id == group_id)