Hi,
We've got a CMS model with a revisioned page tree. Our pages have a
revision number and a parent page (it's a self-referential mapper).
One property we'd like to have in our assign_mapper call is a
latest_parent relation -- a property that returns the page with
page_id = parent_id and revision_number = MAX revision_number for that
page_id.
We've isolated our case into a simple example, where our table looks
like this:
pages_table = Table('pages', metadata,
Column('page_id', Integer, primary_key=True),
Column('revision_number', Integer, primary_key=True),
Column('parent_id', Integer, ForeignKey('pages.page_id'),
nullable=True),
)
We got the property working in the following complete example:
http://dpaste.com/hold/9205/
However, the query generated by that relation has 2 SELECTs, and the
result we want is possible with a single, very simple SELECT (using
page_id = 3 as an example):
"SELECT *, MAX(pages.revision_number) FROM pages WHERE pages.page_id =
3"
...and we can (sort-of) accomplish this in SQLAlchemy with this
select():
select(["*", func.max(pages_table.c.revision_number)],
pages_table.c.page_id == 3)
However, that returns the tuple given by dbapi and not an instance of
the mapped class. We also can't figure out how to get this behavior in
a relation(), so that just the above SELECT is executed and not nested
SELECTs (as in the pasted example).
Is there some other kind of MapperProperty besides relation that will
let us do this? Any help is appreciated.
Brian Beck / Adventurer of the First order / www.brianbeck.com
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---
