On Fri, Dec 5, 2014 at 1:30 AM, Jonathan Vanasco <jvana...@gmail.com> wrote:
> This is a simplified example of my issue. I wrote a detailed example, but
> it might be too confusing.
>
> There are three classes:
>
> Entity
> """only has one of user_id or username"""
> id
> user_id
> username
> profile = relationship( EntityProfile based on user_id )
>
> EntityAlias
> """relates Entity2Entity"""
> id
> entity_user_id
> entity_username
> profile = associationproxy( EntityProfile based on the .profile of
> the entity_user_id )
>
> EntityProfile
> id
> payload
>
> The issue that I've encountered, is that EntityAlias is inherently a
> Many-to-Many table -- but I only want the most recent record when computing
> the join. So i need to somehow sort the EntityAlias table on the join, so
> that I only have the most recent item when calling Entity.profile
>
> Apologies if this is in the docs somewhere -- I can't find it. I've been
> looking at the associationproxies and advanced mapper cases all day, and
> everything looks the same now.
>
I did something similar to this a few years ago. The relationship was
only 1-to-many but hopefully you can adapt it to your situation. I was
storing versioned documents, and I wanted my Document class to have a
property pointing at the most recent Version. Here's the code snippet:
# Add a property to Document which always points at the most recent
# version
_d = Document.__table__
_v = Version.__table__
max_version_id = sa.select([_v.c.id],
_v.c.document_id == _d.c.id,
from_obj=[_v],
order_by=[sa.desc(_v.c.version)],
limit=1)
Document.latest_version = saorm.relationship(
Version,
primaryjoin=Version.id == max_version_id.correlate(_d),
viewonly=True,
uselist=False,
)
The idea was to create a correlated subquery with "ORDER BY version
DESC LIMIT 1" to get the id of the latest version for a given document
id, then use that subquery in the primaryjoin condition for the
relationship.
I've no idea whether this is efficient - it works in my application
but I only have a few thousand documents and perhaps 10 times as many
versions.
(This code was written more than 4 years ago and I barely remember
doing it - I wouldn't be surprised if I had from this list at the
time)
Hope that helps,
Simon
--
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
