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.

score 4 · Accepted Answer · answered Jun 13 '16 at 19:13

4

You need to do a join:

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

answered Jun 13 '16 at 19:13

univerio

19,548
3
66
68

I thought that might be the case, but before I was using 'with_entities' I was just getting back all the columns, and I **was** able to access OpenChromatinRegion.gene.ENSEMBLID. Does that mean the join was already happening in the background? – Alex Lenail Jun 13 '16 at 19:18
@AlexLenail It means it's doing `SELECT * FROM open_chromatin_region, gene WHERE ...`. This is called a cross join and produces an enormous number of rows, which is why your request times out. – univerio Jun 13 '16 at 20:55
Actually that's not quite the query I had before.. It was `SELECT open_chromatin_region.id, open_chromatin_region.chromosome, (every other column...) FROM open_chromatin_region WHERE open_chromatin_region.gene_id IN (1, 2, 3, etc...);` It seems like your comment from [my other question](http://stackoverflow.com/questions/37798864/sqlalchemy-group-by-gives-error-column-must-appear-in-the-group-by-clause-or-be?noredirect=1#comment63064484_37798864) makes it seem like maybe a join isn't the proper way? – Alex Lenail Jun 14 '16 at 14:08
@AlexLenail A join is usually the way to go when you want to select columns from another table based on columns in this table. Your other question has been stripped of the specific details (e.g. table names) such that it's difficult to piece together. Why don't you describe the full query you would like to do? – univerio Jun 14 '16 at 17:46

flask-sqlalchemy with_entities and relationships

1 Answers1

Linked