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!

Hardboiled

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()

Renaldo

I get a weird behavior with that solution. I still get all the instances of "WhateverClass" I have in the database, but if I output the whateverClassInstance.containerClassId, it says "5" for all of them. But that's not true... Only a few of WhateverClass(es) have a containerClassId == 5 (like 3 of them out of 300... I get the 300 instances, and, accoring to the output, the 300 have a whateverClassInstance.containerClassId==5... but that's not what the MySQL query browser is saying). Thanks for you help, though. – Hardboiled

Update: I'm stupid... Dumb, dumb, dumb... I went trough all the records in my database and it turns out I was having way more WhateverClass in the ContainerClass with id == 5 than I thought!!! It was working from gecko! Thank you again... – Hardboiled

Querying on the foreign key id defeats the point behind having an ORM. This answer on another thread shows how to do it with has. – Acolyte