Would I be correct in assuming this Version object won't be in the same
identity map as the other Version objects?
On Fri, Jan 31, 2014 at 9:45 PM, Michael Bayer <mike...@zzzcomputing.com>wrote:
>
> On Jan 31, 2014, at 8:51 PM, Josh Kuhn <deontologic...@gmail.com> wrote:
>
> This is pretty amazing. I get it to *almost* work. The issue seems to be
> that I don't use the database column names as my model attribute names. The
> db column names are really obfuscated, so it's more like:
>
> class Version(Base):
> id = Column('vrsn_nbr', Integer, primary_key=True)
> date = Column('dt', DateTime)
>
> etc..
>
> It seems when this relationship is populated, it creates a Version object,
> but it sets all of the database column names as attributes, and the defined
> Column names are all None. Is there a way to get around that?
>
>
> well if you map to a select() then it uses the column names, as in
> http://docs.sqlalchemy.org/en/rel_0_9/orm/mapper_config.html#naming-columns-distinctly-from-attribute-namesyou
> can rename with properties={“whatever_name”: stmt.c.xyz_column}, etc.
> same thing as saying “id = Column(‘vrsn_nbr’)” on your declarative mapping.
>
>
>
>
>
>
> On Fri, Jan 31, 2014 at 6:38 PM, Michael Bayer
> <mike...@zzzcomputing.com>wrote:
>
>> 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.
>>
>>
>>
>
> --
> 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.
>
>
>
--
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.
