I'm going to add this example to the docs. Let me know if I'm missing something such that it doesn't actually work. On Thu, Sep 27, 2018 at 9:11 PM Mike Bayer <[email protected]> wrote: > > On Thu, Sep 27, 2018 at 5:38 PM Jonathan Vanasco <[email protected]> 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 [email protected]. > > To post to this group, send email to [email protected]. > > 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 [email protected]. To post to this group, send email to [email protected]. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
