On Thu, Sep 27, 2018 at 5:38 PM Jonathan Vanasco <jvana...@gmail.com> wrote: > > I have a handful of relationships where there could be 100s of matching rows, > but in most views I only want to see a subset of them. perhaps the most > recent 5 items of 500 candidates.. > > to handle this so far, i've been constructing a join with correlated > subquery. it works, but this doesn't seem efficient to write or execute. > > does anyone have an idea for a better approach? It feels wonky to > reimplement similar queries so much in my model definition. > > i don't want to use `dynamic` loading, because that relationships strategy > returns a query object and exposes a slightly different usage pattern.. for > various compatibility reasons, I need the relationship attribute to represent > a fully loaded collection. it can be view-only collection - but must be > limited to a certain number of elements.
good news! I almost never do anything smart anymore as I am old and tired, but I thought of something cool here! use a window function! works great and should work with any kind of loader too! has to do the non-primary mapper thing, since you can't introduce the window function in the WHERE or ON clause, but that's no big deal, seems to work!! from sqlalchemy import * from sqlalchemy.orm import * from sqlalchemy.ext.declarative import declarative_base Base = declarative_base() class A(Base): __tablename__ = 'a' id = Column(Integer, primary_key=True) data = Column(String) bs = relationship("B") class B(Base): __tablename__ = 'b' id = Column(Integer, primary_key=True) a_id = Column(ForeignKey("a.id")) data = Column(String) def __repr__(self): return "B(id=%r, a_id=%r)" % (self.id, self.a_id) partition = select([ B, func.row_number().over(order_by=B.id, partition_by=B.a_id).label('index') ]).alias() partitioned_b = mapper(B, partition, non_primary=True) A.partitioned_bs = relationship( partitioned_b, primaryjoin=and_(partitioned_b.c.a_id == A.id, partitioned_b.c.index < 10) ) e = create_engine("postgresql://scott:tiger@localhost/test", echo=True) Base.metadata.drop_all(e) Base.metadata.create_all(e) s = Session(e) s.add_all([ A( id=i, bs=[B(id=j + (i * 100)) for j in range(1, 100)] ) for i in range(1, 10) ]) s.commit() for a1 in s.query(A).options(selectinload(A.partitioned_bs)): print(a1.partitioned_bs) > > > -- > SQLAlchemy - > The Python SQL Toolkit and Object Relational Mapper > > http://www.sqlalchemy.org/ > > To post example code, please provide an MCVE: Minimal, Complete, and > Verifiable Example. See http://stackoverflow.com/help/mcve for a full > description. > --- > 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 sqlalchemy+unsubscr...@googlegroups.com. > To post to this group, send email to sqlalchemy@googlegroups.com. > Visit this group at https://groups.google.com/group/sqlalchemy. > For more options, visit https://groups.google.com/d/optout. -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- 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 sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.