well I can get it to work for lazy loading like this:
expr = select([func.max(Version.id)]).\
where(Version.object_id == Object.id).\
correlate_except(Version).as_scalar()
Object.current_version = relationship(Version,
primaryjoin=and_(
expr == Version.id,
Version.object_id ==
Object.id
)
)
but for joined load, the criteria needs to fit into a LEFT OUTER JOIN ON
clause. Both SQLite and Postgresql reject an aggregate function in the ON
clause. So I didn’t really know how to get that because you have to think in
terms of the SQL….but then the usual approach is that you need to JOIN to a
subquery that has the aggregate inside of it. So I use instead the pattern you
see here:
http://docs.sqlalchemy.org/en/rel_0_9/orm/tutorial.html#using-subqueries which
is the subquery of “X.foo_id, func.AGGREGATE(X.id)” that then joins to the
parent table, and then I go with “non primary mapper”, a use case that I
recently added to the documentation at
http://docs.sqlalchemy.org/en/rel_0_9/orm/relationships.html#relationship-to-non-primary-mapper,
even though this has been around for years.
and it looks like……
expr = select([
func.max(Version.id).label("max_id"),
Version.object_id
]).group_by(Version.object_id).alias()
stmt = select([Version]).\
select_from(join(Version, expr, Version.id == expr.c.max_id)).\
alias()
current_version = mapper(Version, stmt, non_primary=True)
Object.current_version = relationship(current_version)
I think I might have actually written a mapping like this as an example back in
SQLAlchemy 0.1 even, this was the goofy kind of thing I thought everyone would
be doing all the time.
works with joinedload. Query is not too efficient, but is like:
SELECT objects.id AS objects_id, anon_1.id AS anon_1_id, anon_1.object_id AS
anon_1_object_id
FROM objects LEFT OUTER JOIN (SELECT versions.id AS id, versions.object_id AS
object_id
FROM versions JOIN (SELECT max(versions.id) AS max_id, versions.object_id AS
object_id
FROM versions GROUP BY versions.object_id) AS anon_2 ON versions.id =
anon_2.max_id) AS anon_1 ON objects.id = anon_1.object_id
WHERE objects.id = %(id_1)s
On Jan 31, 2014, at 5:35 PM, Josh Kuhn <deontologic...@gmail.com> wrote:
> I've got a two tables I'd like to create a relationship for. One is the
> object, and another tracks versions.
>
> Here's a gist with the setup:
> https://gist.github.com/deontologician/8744532
>
> Basically, the object doesn't have a direct reference to the current version
> stored in the table. Instead, the current version is defined as the maximum
> version that points to that object.
>
> I'd like to have a one-to-one "current_version" relationship, but this has
> proven difficult (at least in 0.8.4). The primary goal is to allow using the
> joinedload options to control populating the current_version field, but that
> only works when a relationship is defined and is non-dynamic.
>
> Any hints as to how to get this to work like I want?
>
> --
> 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/groups/opt_out.
signature.asc
Description: Message signed with OpenPGP using GPGMail
