graN graN - 2 months ago 23
MySQL Question

Optimizing hybrid_properties in SQLAlchemy

I have a piece of working code but it is very inefficient, instead of a single query with a join. I get one initial query, followed by one query per row in the response.

I have to following scenario:

class Job(Base, SerializeMixin, JobInterface):
__tablename__ = 'job_subjobs'

id = Column(Integer, primary_key=True, autoincrement=True)
group_id = Column(Integer, ForeignKey("job_groups.id"), nullable=False)

class Crash(Base, SerializeMixin):
__tablename__ = 'crashes'

id = Column(Integer, primary_key=True, autoincrement=True)
job_id = Column(Integer, ForeignKey("job_subjobs.id", ondelete='CASCADE'), nullable=False)

job = relationship('Job', backref='Crash')

@hybrid_property
def job_identifier(self):
return "{}:{}".format(self.job.group_id, self.job.id)


So given the above and I perform a query for all Crashes, It will perform one SELECT for all crashes. When I iterate and ask for job_identifier it will then do one separate SELECT for each crash.

self.session.query(Crash).all()


Is there someway i can create a @hybrid_property referencing a different table and have it JOIN from the beginning and preload the expression?

I've experimented with @xxx.expression without success. If all else fails I can add another foreign key in Crash table, but I would like to avoid changing current data structure if possible.

Answer

ended up using:

jobs = relationship('Job', backref='Crash', lazy='joined')
Comments