如何定义表示集合中最新对象的sqlalchemy关系?

纯粹的关系方法需要使用子查询来获取与父查询相关的“最新”或“最大”值,然后将其与集合成员相等。这意味着,如果在确定“最新”的列上放置索引,将获得最佳结果:

from sqlalchemy import *
from sqlalchemy.orm import *

engine = create_engine('sqlite:///:memory:', echo='debug')

m = MetaData()

parent = Table('parent', m, 
                Column('id', Integer, primary_key=True)
)

child = Table('child', m, 
                Column('id', Integer, primary_key=True),
                Column('parent_id', Integer, ForeignKey('parent.id')),
                Column('sortkey', Integer)
                )

m.create_all(engine)

class Parent(object):
    def __init__(self, children):
        self.all_c = children

class Child(object):
    def __init__(self, sortkey):
        self.sortkey = sortkey

latest_c = select([func.max(child.c.sortkey)]).\
                where(child.c.parent_id==parent.c.id).\
                correlate(parent).\
                as_scalar()

mapper(Parent, parent, properties={
    'all_c':relation(Child),
    'latest_c':relation(Child, 
                            primaryjoin=and_(
                                child.c.sortkey==latest_c, 
                                child.c.parent_id==parent.c.id
                            ),
                            uselist=False
    )
})

mapper(Child, child)

session = sessionmaker(engine)()

p1, p2, p3 = Parent([Child('a'), Child('b'), Child('c')]), \
                Parent([Child('b'), Child('c')]),\
                Parent([Child('f'), Child('g'), Child('c')])

session.add_all([p1, p2, p3])
session.commit()

assert p1.latest_c.sortkey == 'c'
assert p2.latest_c.sortkey == 'c'
assert p3.latest_c.sortkey == 'g'

或者,您可以在某些平台上使用limit,这可以产生更快的结果,因为您可以避免聚合,并可以在其主键上加入集合项:

latest_c = select([child.c.id]).\
                where(child.c.parent_id==parent.c.id).\
                order_by(child.c.sortkey.desc()).\
                limit(1).\
                correlate(parent).\
                as_scalar()

mapper(Parent, parent, properties={
    'all_c':relation(Child),
    'latest_c':relation(Child, 
                            primaryjoin=and_(
                                child.c.id==latest_c, 
                                child.c.parent_id==parent.c.id
                            ),
                            uselist=False
    )
})