1. 单个字段排序

# 升序排序（默认）
results = Engword.query.order_by(Engword.created_at).all()

# 降序排序
results = Engword.query.order_by(Engword.created_at.desc()).all()

2. 多个字段排序

# 先按category升序，再按created_at降序
results = Engword.query.order_by(
    Engword.category.asc(),
    Engword.created_at.desc()
).all()

# 或者混合使用
results = Engword.query.order_by(
    Engword.category,  # 默认升序
    Engword.frequency.desc()
).all()

3. 只返回特定字段

from sqlalchemy.orm import load_only

# 只返回id和word字段，并按word排序
results = Engword.query.options(
    load_only(Engword.id, Engword.word)
).order_by(Engword.word.asc()).all()

# 对于每个结果，只能访问 id 和 word 字段
for result in results:
    print(result.id, result.word)
    # print(result.created_at)  # 这会报错，因为没加载这个字段

4. 使用 with_entities 指定字段

from sqlalchemy.orm import Query

# 返回特定字段的元组列表
results = Engword.query.with_entities(
    Engword.id,
    Engword.word,
    Engword.meaning
).order_by(Engword.word.asc()).all()

for id, word, meaning in results:
    print(f"{id}: {word} - {meaning}")

5. 使用字典形式返回

from sqlalchemy.orm import aliased

# 返回字典形式的结果
results = Engword.query.with_entities(
    Engword.id.label('word_id'),
    Engword.word,
    Engword.pronunciation
).order_by(Engword.word.asc()).all()

for result in results:
    print(f"ID: {result.word_id}, Word: {result.word}")

6. 带条件的排序查询

# 查询特定类别的单词并按频率排序
results = Engword.query.filter(
    Engword.category == 'technology'
).order_by(Engword.frequency.desc()).all()

# 查询包含某个字母的单词并按长度排序
results = Engword.query.filter(
    Engword.word.like('%a%')
).order_by(
    Engword.word_length.asc(),
    Engword.word.asc()
).all()

7. 分页查询

# 获取第2页，每页50条，按创建时间倒序
page = 2
per_page = 50
results = Engword.query.order_by(
    Engword.created_at.desc()
).offset((page - 1) * per_page).limit(per_page).all()

8.自定义排序规则

from sqlalchemy import case

# 按自定义优先级排序：先显示特定类别的单词
custom_order = case(
    {
        'basic': 1,
        'advanced': 2,
        'technical': 3
    },
    value=Engword.category
)
results = Engword.query.order_by(custom_order.asc()).all()

9. 按关联模型字段排序

# 如果Engword有关联的Category模型
from sqlalchemy.orm import joinedload

results = Engword.query.options(
    joinedload(Engword.category)
).order_by(
    Engword.category.name.asc(),  # 按关联的category名称排序
    Engword.word.asc()
).all()

10. 按计算字段排序

from sqlalchemy import func

# 按单词长度排序
results = Engword.query.order_by(
    func.length(Engword.word).asc()
).all()

# 按创建时间的年月排序
results = Engword.query.order_by(
    func.date_format(Engword.created_at, '%Y-%m').desc()
).all()

示例1：获取单词表并按字母顺序排列

def get_words_alphabetically():
    """按字母顺序返回所有单词"""
    return Engword.query.with_entities(
        Engword.id,
        Engword.word,
        Engword.meaning
    ).order_by(Engword.word.asc()).all()

示例2：获取最常用的单词

def get_most_frequent_words(limit=100):
    """获取使用频率最高的单词"""
    return Engword.query.order_by(
        Engword.frequency.desc()
    ).limit(limit).all()

示例3：按类别分组排序

def get_words_by_category():
    """按类别分组，每类按字母排序"""
    return Engword.query.order_by(
        Engword.category.asc(),
        Engword.word.asc()
    ).all()

示例4：搜索并排序

def search_words(keyword, sort_by='word'):
    """搜索单词并按指定字段排序"""
    query = Engword.query.filter(
        Engword.word.ilike(f'%{keyword}%')
    )

    # 动态排序
    if sort_by == 'frequency':
        query = query.order_by(Engword.frequency.desc())
    elif sort_by == 'length':
        query = query.order_by(func.length(Engword.word).asc())
    else:  # 默认按单词排序
        query = query.order_by(Engword.word.asc())

    return query.all()

性能优化建议

1. 为排序字段添加索引

# 在模型定义中，确保常用排序字段有索引
class Engword(db.Model):
    __tablename__ = 'engwords'

    id = db.Column(db.Integer, primary_key=True)
    word = db.Column(db.String(100), index=True)  # 为排序字段添加索引
    frequency = db.Column(db.Integer, index=True)
    created_at = db.Column(db.DateTime, index=True)

2. 只选择需要的字段

# 避免 SELECT *，只选择需要的字段
results = Engword.query.with_entities(
    Engword.id,
    Engword.word
).order_by(Engword.word.asc()).all()

3. 使用分页避免内存溢出

# 对于大量数据，使用分页
def get_words_in_batches(batch_size=1000):
    """分批获取单词"""
    page = 1
    while True:
        words = Engword.query.order_by(
            Engword.id.asc()
        ).paginate(page=page, per_page=batch_size)

        if not words.items:
            break

        yield words.items
        page += 1