Cyril Scetbon <[email protected]> wrote:
> 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’) this primaryjoin is not necessary. Because you are creating a relationship to “Account”, it will query out to a JOIN of the batch_record and account tables automatically which will limit the rows to those with a type of “account”. Only if you’re trying to exclude rows from some other class that is a subclass of “Account” would this be at all necessary but that would be unusual. > 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' this is occurring because the relationship is not aware that this is a so-called “simple many-to-one”, for which is can do a straight primary key lookup in the identity map. Because your custom “primaryjoin” condition is also shared on the many-to-one side, it assumes there is special SQL that must be emitted to ensure the correct results, and a simple identity lookup is not possible. the two configurations that will solve this issue are: accounts = db.relationship('Account', backref='batch’) or alternatively, if you really wanted to keep that primaryjoin: accounts = db.relationship('Account', primaryjoin="and_(Batch.id == BatchRecord.batch_id, BatchRecord.type == 'account')", backref=backref(‘batch’, primaryjoin=None)) > > 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 [email protected]. > To post to this group, send email to [email protected]. > Visit this group at http://groups.google.com/group/sqlalchemy. > For more options, visit https://groups.google.com/d/optout. -- 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 [email protected]. To post to this group, send email to [email protected]. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
