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.
