SQLAlchemy: Query filter including extra data from association table

Question

I am trying to build an ORM mapped SQLite database. The conception of the DB seems to work as intended but I can't seem to be able to query it properly for more complex cases. I have spent the day trying to find an existing answer to my question but nothing works. I am not sure if the issue is with my mapping, my query or both. Or if maybe querying with attributes from a many to many association table with extra data works differently.

This the DB setup:

engine = create_engine('sqlite:///')
Base = declarative_base(bind=engine)
Session = sessionmaker(bind=engine)


class User(Base):
    __tablename__ = 'users'

    # Columns
    id = Column('id', Integer, primary_key=True)
    first = Column('first_name', String(100))
    last = Column('last_name', String(100))
    age = Column('age', Integer)
    quality = Column('quality', String(100))
    unit = Column('unit', String(100))

    # Relationships
    cases = relationship('UserCaseLink', back_populates='user_data')

    def __repr__(self):
        return f"<User(first='{self.first}', last='{self.last}', quality='{self.quality}', unit='{self.unit}')>"


class Case(Base):
    __tablename__ = 'cases'

    # Columns
    id = Column('id', Integer, primary_key=True)
    num = Column('case_number', String(100))
    type = Column('case_type', String(100))

    # Relationships
    users = relationship('UserCaseLink', back_populates='case_data')

    def __repr__(self):
        return f"<Case(num='{self.num}', type='{self.type}')>"


class UserCaseLink(Base):
    __tablename__ = 'users_cases'

    # Columns
    user_id = Column('user_id', Integer, ForeignKey('users.id'), primary_key=True)
    case_id = Column('case_id', Integer, ForeignKey('cases.id'), primary_key=True)
    role = Column('role', String(100))

    # Relationships
    user_data = relationship('User', back_populates='cases')
    case_data = relationship('Case', back_populates='users')


if __name__ == '__main__':
    Base.metadata.create_all()
    session = Session()

and I would like to retrieve all the cases on which a particular person is working under a certain role. So for example I want a list of all the cases a person named 'Alex' is working on as an 'Administrator'. In other words I would like the result of this query:

SELECT [cases].*,
       [main].[users_cases].role
FROM   [main].[cases]
       INNER JOIN [main].[users_cases] ON [main].[cases].[id] = [main].[users_cases].[case_id]
       INNER JOIN [main].[users] ON [main].[users].[id] = [main].[users_cases].[user_id]
WHERE  [main].[users].[first_name] = 'Alex'
AND [main].[users_cases].[role] = 'Administrator';

So far I have tried many things along the lines of:

cases = session.query(Case).filter(User.first == 'Alex', UserCaseLink.role == 'Administrator')

but it is not working as I would like it to.

How can I modify the ORM mapping so that it does the joining for me and allows me to query easily (something like the query I tried)?

Answer 1

So I ended up having to compromise. It seems the query cannot be aware of all the relationships present in the ORM mapping at all times. Instead I had to manually give it the path between the different classes for it to find all the data I wanted:

cases = session.query(Case)\
               .join(Case.users)\
               .join(UserCaseLink.user_data)\
               .filter(User.first == 'Alex', UserCaseLink.role == 'Administrator')\
               .all()

However, as it does not meet all the criteria for my original question (ie I still have to specify the joins), I will not mark this answer as the accepted one.

Answer 2

According to your calsses, the quivalent query for:

SELECT [cases].*,
       [main].[users_cases].role
FROM   [main].[cases]
       INNER JOIN [main].[users_cases] ON [main].[cases].[id] = [main].[users_cases].[case_id]
       INNER JOIN [main].[users] ON [main].[users].[id] = [main].[users_cases].[user_id]
WHERE  [main].[users].[first_name] = 'Alex'
AND [main].[users_cases].[role] = 'Administrator';

is

cases = session.query(
    Case.id, Case.num,Cas.type,
    UserCaseLink.role
    ).filter(
    (Case.id==UserCaseLink.case_id)
    &(User.id==UserCaseLink.user_id)
    &(User.first=='Alex')
    &(UserCaseLink.role=='Administrator'
    ).all()

also, you can:

cases = Case.query\
    .join(UserCaseLink,Case.id==UserCaseLink.case_id)\
    .join(User,User.id==UserCaseLink.user_id)\
    .filter( (User.first=='Alex') & (User.first=='Alex') )\
    .all()

Good Luck

After comment

based in your comment, I think you want something like:

cases = Case.query\
    .filter( (Case.case_data.cases.first=='Alex') & (Case.case_data.cases.first=='Alex') )\
    .all()

where case_data connect between Case an UserCaseLink and cases connect between UserCaseLink and User as in your relations.

But,that case causes error: AttributeError: Neither 'InstrumentedAttribute' object nor 'Comparator' object associated with dimpor.org_type has an attribute 'org_type_id' The missage shows that the attributes combined in filter should belong to the table class