Hey, I got some shop tables like: customerorders, itemsbought, products whole query with three joins takes nothing on command line sql. When I trying to achieve same thing with ORM and my relationship it takes 1 second, joining all three 3 tables 2 seconds?
May I come first up with my table and mapper definitions: class Products(Base): __tablename__ = 'products' id = Column(Integer, primary_key=True) productname = Column(Unicode(255)) productname_en =Column(Unicode(255), default = u'nice product') path = Column(Unicode(300)) pic = Column(Unicode(300), unique=True) bigpic = Column(Unicode(300), unique=True) sold = Column(Boolean) price = Column(Integer) locked = Column(Boolean) sort = Column(Integer) catid = Column(Integer) sizeid = Column(Integer, ForeignKey('productsizes.sizeid')) productsizes = relationship("ProductSizes", backref="products", lazy='joined') itemsbought = relationship("Itemsbought", backref="products") def __init__(self, productname, path, pic, bigpic, sold, price, sizeid, locked, sort, catid): self.productname = productname self.path = path self.pic = pic self.bigpic = bigpic self.sold = sold self.price = price self.sizeid = sizeid self.locked = locked self.sort = sort self.catid = catid class Customerorder(Base): __tablename__ = 'customerorders' id = Column(Integer, primary_key=True) ip = Column(Unicode(40)) comment = Column(Unicode(1000)) plz = Column(Unicode(30)) name = Column(Unicode(100)) street = Column(Unicode(200)) ort = Column(Unicode(100)) date = Column(DateTime, default = func.now()) money_received = Column(Boolean) shipped = Column(Boolean) def __init__(self, comment, ip, street, plz, ort, name, money_received): self.ip = ip self.customerid = customerid self.comment = comment self.street = street self.plz = plz self.ort = ort self.name = name self.money_received = money_received self.shipped = shipped class Itemsbought(Base): __tablename__ = 'itemsbought' id = Column(Integer, primary_key=True) orderid = Column(Integer, ForeignKey('customerorders.id')) order = relationship('Customerorder', backref=backref("itemsbought")) productid = Column(Integer, default = 0) def __init__(self, productid, orderid): self.productid = productid self.orderid = orderid I try to query only Customerorder and Itemsbought without having those relations to products: customerorders = sqlsession.query(Customerorder)\ .join(Itemsbought.order)\ .all() or with joinedload, outerjoins all the same thing. log shows me always sqlalchemy makes left outer joins. Trying to use this data in loop ends up in 1700 queries: for order in customerorders: print order.name, for item in order.itemsbought: print item.productid I managed to get all this correct data with sizes and products in 2,5 s with joinedload but this is a way too long for 5 k entries in itemsbought and 1680 orders. Can someone point to what I am doin wrong? And whats the best way to get all orders in an clearly arranged way with products, price, sizes etc. Cheers -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.