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 -~----------~----~----~----~------~----~------~--~---