On Feb 3, 2014, at 11:00 AM, Josh Kuhn <deontologic...@gmail.com> wrote:
> Would I be correct in assuming this Version object won't be in the same
> identity map as the other Version objects?
no, the identity mapping is based on the class + primary key, so as long as the
primary key consists of the same columns (i don’t have the example in front of
me, but you need to make sure PK cols from the other tables aren't present in
the SELECT, else you should use mapper.primary_key config to explicitly define
which cols comprise the primary key) it should be the same.
>
>
> 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-names
> you 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.
signature.asc
Description: Message signed with OpenPGP using GPGMail
