On Apr 27, 2007, at 9:32 PM, Brian Beck wrote:
>
> 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.
create your Mapper against that select statement, instead of the
table. for example (a very old example),
http://www.sqlalchemy.org/trac/browser/zblog/trunk/lib/zblog/database/
mappers.py#L45
alternatively, the aggregate can be added at the query level:
session.query(MyClass).add_column(func.max(somecol)).filter_by
(wahtever).group_by([c for c in MyClass.c]).list()
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to [email protected]
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
-~----------~----~----~----~------~----~------~--~---
