SqlAlchemy - Filtering by field defined as a ForeignKey

Question

I am trying to get instances of a class filtered by a field which is a foreign key but when I try to do that, I always get all the entries in the database, instead of the ones that match the criterion.

Let's say I have a couple of classes using declarative base in a simple relationship N:1. I have that modeled like:

#!/usr/bin/python2.6
class ContainerClass(declarativeBase):
     __tablename__ = "container_classes"
     _id = Column("id", Integer, primary_key=True)
     id = sqlalchemy.orm.synonym('_id', descriptor=property(getId, setId))


class WhateverClass(declarativeBase):
     __tablename__ = "whatever_classes"

     _id = Column("id", Integer, primary_key=True)
     _total = Column("total", Integer)
     _containerClassId = Column("container_class_id", Integer, ForeignKey("other_classes.id"))

     _containerClass = relationship("ContainerClass", uselist=False)

     id = sqlalchemy.orm.synonym('_id', descriptor=property(getId, setId))
     total = sqlalchemy.orm.synonym('_total', descriptor=property(getTotal, setTotal))
     containerClassId = sqlalchemy.orm.synonym('_containerClassId', decriptor=property(getContainerClassId, setContainerClassId))
     containerClass = sqlalchemy.orm.synonym('_containerClass', descriptor=property(getContainerClass setContainerClass))

An instance of "WhateverClass" can belong to one instance of "ContainerClass" (and each ContainerClass can have several instances of WhateverClass). The relationship seems to be working fine. If I add a new "WhateverClass" to a "ContainerClass", the whateverClass.containerClassId properly gets the value of the ContainerClass it belongs to.

But let's say I need to get a list of instances of "WhateverClass" that belong to the "ContainerClass" whose id==5.

If I try to do:

from myClasses import WhateverClass
session.query(WhateverClass.WhateverClass).filter(WhateverClass.WhateverClass.containerClass.id == 5).all()

I get all the instances of WhateverClass that are stored in the database, not only the ones who are linked to the ContainerClass with id == 5

But if I do session.query(WhateverClass.WhateverClass).filter(total <= 100).all() I properly get instances of WhateverClass whose "total" field is <=100.

I'm pretty newbie to sqlalchemy... Do I have to use a joined query or something like that? I know it can't be complicated, but I haven't been able to find any suitable solution. I've tried joins, unions... But (obviously) I'm doing something wrong.

I'm using SqlAlchemy 0.6.6 and Python 2.6 (just in case it's relevant)

Thank you in advance!

Answer 1

For a simple query, you can just query directly:

session.query(WhateverClass).filter(WhateverClass._containerClassId == 5).all()

For more complex relationships, you need a join:

session.query(WhateverClass).join(ContainerClass).filter(ContainerClass.id == 5).all()