On 03/30/2016 11:56 AM, Jonathan Vanasco wrote:
On Wednesday, March 30, 2016 at 12:46:01 AM UTC-4, Mike Bayer wrote:
why can't you build a relationship() which has its primaryjoin set
up to
do this? You can do it w/ a correlated subquery.
Can you reference an example ?
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)
bs = relationship("B")
class B(Base):
__tablename__ = 'b'
id = Column(Integer, primary_key=True)
sort_key = Column(Integer)
a_id = Column(ForeignKey('a.id'))
def __repr__(self):
return "B(sort_key=%s)" % self.sort_key
A.last_five_bs = relationship(
B,
primaryjoin=(
and_(
A.id == B.a_id,
B.sort_key.in_(
select([B.sort_key]).
where(A.id == B.a_id).
order_by(B.sort_key.desc()).limit(5)
)
)
),
order_by=B.sort_key.desc(),
viewonly=True
)
e = create_engine("sqlite://", echo=True)
Base.metadata.create_all(e)
s = Session(e)
s.add(
A(bs=[
B(sort_key=1),
B(sort_key=2),
B(sort_key=3),
B(sort_key=4),
B(sort_key=5),
B(sort_key=6),
B(sort_key=7),
B(sort_key=8)
])
)
s.commit()
print s.query(A).first().last_five_bs
That is, unless you want this relationship() to load differently up
front with options. if you just want the limit() to change, put a
bindparam() inside of limit() and then use this approach:
I should have stated my goals differently:
on ItemView, I need to load & iterate the last 5 items on the relationship
on OtherViews, I will eventually need to note `contains_eager`.
I just used the dynamic relationship for now, along with a property that
loads/caches the last 5. this allows me to hit the relationship
multiple times with only one load. (see below)
the actual context, btw, is a tool for managing LetsEncrypt certificate
deployments.
---
certificate_requests =
sa.orm.relationship("LetsencryptCertificateRequest",
primaryjoin="LetsencryptAccountKey.id==LetsencryptCertificateRequest.letsencrypt_account_key_id",
back_populates='letsencrypt_account_key',
order_by='LetsencryptCertificateRequest.id.desc()',
lazy="dynamic",
)
@property
def certificate_requests_5(self):
if self._certificate_requests_5 is None:
self._certificate_requests_5 = self.certificate_requests\
.options(sa.orm.joinedload('certificate_request_to_domains').joinedload('domain'),
)\
.limit(5)\
.all()
return self._certificate_requests_5
_certificate_requests_5 = None
--
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
<mailto:sqlalchemy+unsubscr...@googlegroups.com>.
To post to this group, send email to sqlalchemy@googlegroups.com
<mailto:sqlalchemy@googlegroups.com>.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
--
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.
