Re: [sqlalchemy] Relationships using max
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.
Re: [sqlalchemy] Relationships using max
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
Re: [sqlalchemy] Relationships using max
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