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.
