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