Hi, After having read http://docs.sqlalchemy.org/en/latest/orm/loading_relationships.html I understand there is one case where SQL is not emitted and I was expecting that my case was this one.
I use polymorphism to store different objects in the same table (only one type displayed here) as follows : class BatchRecord(db.Model): __tablename__ = 'batch_record' id = db.Column(db.Integer, primary_key=True, nullable=False) batch_id = db.Column(db.Integer, db.ForeignKey('batch.id'), nullable=False) type = db.Column(db.String(15)) created = db.Column(db.DateTime, default=datetime.utcnow) modified = db.Column(db.DateTime, default=datetime.utcnow, onupdate=datetime.utcnow) class Batch(db.Model): __tablename__ = 'batch' id = db.Column(db.Integer, primary_key=True, nullable=False) source = db.Column(db.String(10)) created = db.Column(db.DateTime, default=datetime.utcnow) modified = db.Column(db.DateTime, default=datetime.utcnow, onupdate=datetime.utcnow) batch_records = db.relationship('BatchRecord', cascade='all,delete-orphan') accounts = db.relationship('Account', primaryjoin="and_(Batch.id == BatchRecord.batch_id, BatchRecord.type == 'account')", backref='batch') class Account(BatchRecord): __tablename__ = 'account' id = db.Column(db.Integer, db.ForeignKey('batch_record.id'), primary_key=True, nullable=False) uuid = db.Column(UUID, nullable=False) role = db.Column(db.String(15), nullable=False) first_name = db.Column(db.String(40)) last_name = db.Column(db.String(40)) email = db.Column(db.String(80)) phone = db.Column(db.String(40)) cohort = db.Column(db.String(255)) The code I use is similar to the following : (1) batch = Batch.query.filter(Batch.id == 50048).first() (2) a0 = batch.accounts[0] (3) a0.batch.source (4) a1 = batch.accounts[1] (5) a1.batch.source at (1) SqlAlchemy requests the database (PostgreSQL) to get the batch object using the query SELECT batch.id AS batch_id, batch.source AS batch_source, batch.created AS batch_created, batch.modified AS batch_modified FROM batch WHERE batch.id = 50048 LIMIT 1 at (2) it does the following query to get account objects : SELECT account.id AS account_id, batch_record.id AS batch_record_id, batch_record.batch_id AS batch_record_batch_id, batch_record.type AS batch_record_type, batch_record.created AS batch_record_created, batch_record.modified AS batch_record_modified, account.uuid AS account_uuid, account.role AS account_role, account.first_name AS account_first_name, account.last_name AS account_last_name, account.email AS account_email, account.phone AS account_phone, account.cohort AS account_cohort FROM batch_record JOIN account ON batch_record.id = account.id WHERE 50048 = batch_record.batch_id AND batch_record.type = 'account' and the issue is at (3). It does a the new following query : SELECT batch.id AS batch_id, batch.source AS batch_source, batch.created AS batch_created, batch.modified AS batch_modified FROM batch WHERE batch.id = 50048 AND 'account' = 'account' SqlAlchemy should know using account.batch_id (which was stored in the object at (2)) that it references the batch object requested at (1) and should not request the database again to get information it already has (at (1)). at (4) it does not request the database but at (5) it requests again the database for the same object : SELECT batch.id AS batch_id, batch.source AS batch_source, batch.created AS batch_created, batch.modified AS batch_modified FROM batch WHERE batch.id = 50048 AND 'account' = 'account' The matter is that we have thousand of objects and SqlAlchemy requests the database 1 time per object :( FYI we're using the stable release (0.9.8), but I've tested the last pre-released version (1.0.0b1) and the behavior is exactly the same. -- 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.