flask-sqlalchemy with_entities and relationships

Question

I want to select only a couple columns from my model to speed up the queries, but one of the columns I want to select is from a relationship.

Models:

class OpenChromatinRegion(db.Model):
    ...
    gene_id     = db.Column(db.Integer, db.ForeignKey("gene.id"), nullable=False, index=True)
    gene        = db.relationship("Gene", back_populates='open_chromatin_regions')

class Gene(db.Model):
    id          = db.Column(db.Integer, primary_key=True)
    ENSEMBLID   = db.Column(db.Integer, index=True, unique=True, nullable=False)
    ...

Query:

q = OpenChromatinRegion.query.with_entities(Gene.ENSEMBLID, ...)...

How do I properly select only a couple columns from OpenChromatinRegion using flask-sqlalchemy I previously tried .with_entities(OpenChromatinRegion.gene.ENSEMBLID) but that didn't work either. With this syntax, I don't get an error but the request times out.

Answer 1

You need to do a join:

q = OpenChromatinRegion.query.join(OpenChromatinRegion.gene) \
                             .with_entities(Gene.ENSEMBLID)