CODEWITHSUNDEEP
pythonsqlalchemy
user2308191
user2308191

Reputation: 301

How to query fast from nested relationships in SQLAlchemy?

I have these tables in my SQLAlchemy database:

DSource table:

class DSource(Base):
    __tablename__ = 'dsource'

    source_id = Column(Integer, primary_key=True)
    subjects= relationship("Subject")

Subject table:

class Subject(Base):
    __tablename__ = 'subject'

    subject_id = Column(Integer, primary_key=True)
    source_id=Column(Integer, ForeignKey("dsource.source_id"),nullable=False)
    sequences= relationship("DSequence")

DSequence table:

class DSequence(Base):
    __tablename__ = 'dsequence'

    sequence_id = Column(Integer, primary_key=True)
    subject_id = Column(Integer, ForeignKey("subject.subject_id"),nullable=False)
    frames= relationship("Frame")

and Frame table:

class Frame(Base):
    __tablename__ = 'frame'

    f_id = Column(Integer, primary_key=True)
    sequence_id=Column(Integer, ForeignKey("dsequence.sequence_id"),nullable=False)
    action_units=Column(String)
    expression=Column(String)
    pts3d=Column(String)
    pts2d=Column(String)

Now I want to write a query against Frame table that gives me all the frames with source_id=2. One way is to write a nested loop that retrieves all the subjects with dsource=2, then for each of these subjects it finds the sequences and then for each sequence it finds the corresponding frames. 

fr=session.query(Frame,Subject,DSequence,DSource).filter(DSource.source_id==2).all()

But querying against database that has these nested relationship is really slow, because in the Frame table I have around 90,000 frames. is there any way that I can query much faster? I'm not sure if joinedload can help me for this problem.

Upvotes: 3

Views: 4593

Answers (1)

user2308191
user2308191

Reputation: 301

I found the solution, there is a really easy way to do the above query and it is very fast:

session.query(Frame).join(DSequence).join(Subject).join(DSource).filter(DSource.source_id==2).all()

Upvotes: 5

Related Questions