レコードの前後のデータを取得する

http://d.hatena.ne.jp/pyxis-dev/20081001/1222791295

適切なインデックスが張ってあってかつDBMSのソート戦略が十分賢いなら、これで十分のような気がします。
（実行して試したわけではないのでどこか間違ってるかも）

    def get_neighbors(entry):
        m = DiaryModel

        older = m.query.filter(((m.pubdate==entry.pubdate) &&
                                (m.modified_at==entry.modified_at) &&
                                (m.id<entry.id)) ||
                               ((m.pubdate==entry.pubdate) &&
                                (m.modified_at<entry.modified_at)) ||
                               (m.pubdate<entry.pubdate)) \
                       .order_by(desc(m.pubdate),
                                 desc(m.modified_at),
                                 desc(m.id)) \
                       .first()

        newer = m.query.filter(((m.pubdate==entry.pubdate) &&
                                (m.modified_at==entry.modified_at) &&
                                (m.id>entry.id)) ||
                               ((m.pubdate==entry.pubdate) &&
                                (m.modified_at>entry.modified_at)) ||
                               (m.pubdate>entry.pubdate)) \
                       .order_by(m.pubdate,
                                 m.modified_at,
                                 m.id) \
                       .first()

        return [older, newer]

あるいはもっと一般化して、

    def make_filter(mapper, entry, keys, is_desc):
        import operator
        op = is_desc and operator.lt or operator.gt

        filters = []
        for i in reversed(xrange(len(keys))):
            filters.append(
                and_(*([getattr(mapper, key) == getattr(entry, key)
                        for key in keys[0:i]] +
                       [op(getattr(mapper, keys[i]), getattr(entry, keys[i]))])))
        return or_(*filters)

    def make_order_by(mapper, keys, is_desc):
        order_by = []
        for key in keys:
            order_by.append(getattr(mapper, key))
        if is_desc:
            return map(desc, order_by)
        else:
            return order_by

    def get_olders(query, mapper, entry, sort_keys):
        return query.filter(make_filter(mapper, entry, sort_keys, True)) \
                    .order_by(*make_order_by(mapper, sort_keys, True))

    def get_newers(query, mapper, entry, sort_keys):
        return query.filter(make_filter(mapper, entry, sort_keys, False)) \
                    .order_by(*make_order_by(mapper, sort_keys, False))

    def get_neighbors(entry):
        sort_keys = ['pubdate', 'modified_at', 'id']
        query = DiaryModel.query()
        return [get_olders(query, DiaryModel, entry, sort_keys).first(),
                get_newers(query, DiaryModel, entry, sort_keys).first()]