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 [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.
