Amazing:
session.query(Cashier.Cashier).join(Register.Register).join(Store.Store).all()
I hadn't tried before because I thought it would be too straight forward...
2011/2/16 Hector Blanco <white.li...@gmail.com>:
> Hello everyone!
>
> I have a class structure like this:
>
> class Store(declarativeBase):
> __tablename__ = "stores"
>
> id = Column("id", Integer, primary_key=True)
> name = Column("name", String(50))
> registers = relationship("Register", cascade="all, delete",
> collection_class=set)
>
> (One store can have N registers, but a register can be only in one store)
>
> class Register(declarativeBase):
> __tablename__ = "registers"
> id = Column("id", Integer, primary_key=True)
> name = Column("name", String(50))
> cashiers = relationship("Cashier", cascade="all, delete",
> collection_class=set)
>
> (One Register can have many different cashiers assigned, but only one
> cashier can be assigned to a register) Probably in real life this
> would be more a 1:1 relationship... but let's say there's a team of
> cashiers and the can be assigned to a bunch of different registers
>
> And well... finally, the Cashier thingy:
>
> class Cashier(declarativeBase):
> __tablename__ = "cashiers"
> id = Column("id", Integer, primary_key=True)
>
> At a certain point, I need to get the cashiers that are in certains
> stores (I receive the stores' ids as a list/set and I need to get all
> the "Cashier" objects that can be assigned to that store).
>
> The only solution my (limited and newbie) mind has been able to come up with
> is:
>
> 1) Get the stores.
> 2) For each store, get the registers
> 2) For each register, get the cashiers that can be assigned to them
>
> In order to do that, I have create a method like this (let's say the
> "id"s of the stores come in the "storeIds" parameter):
>
> returnValue = set()
> relationshipsToPreload = ["registers", "registers.cashiers"] # For
> the joinedload thing...
> stores = session.query(Store.Store).options( *
> [sqlalchemy.orm.joinedload_all(relationshipToPreLoad) for
> relationshipToPreLoad in
> relationshipsToPreload]).filter(Store.Store.ids.in_(storeId)).all()
> session.close()
> for store in stores:
> for register in store.registers:
> for cashier in register.cashiers:
> returnValue.add(cashier.id)
>
> I would like to know if you have a better approach to do this. I got
> it working though... is mainly out of curiosity. Maybe I can make a
> bunch of joins that may improve the performance...
>
> Thank you in advance!
>
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
