Flask-SQLAlchemy获取用户关注岗位最新报价实现及SQL优化

1. User模型get_followed_jobs方法实现 #

以下是SQLAlchemy ORM的实现方案，兼顾性能和可读性：

from sqlalchemy import func, desc

class User(UserMixin, db.Model):
    # 原有字段和关系保持不变
    def get_followed_jobs(self):
        # 子查询：获取每个岗位对应的最新报价时间戳
        latest_quote_subq = db.session.query(
            Quote.job_id,
            func.max(Quote.timestamp).label('latest_timestamp')
        ).group_by(Quote.job_id).subquery()

        # 关联查询：过滤当前用户关注的岗位，匹配对应最新报价
        query_result = db.session.query(
            Job,
            Quote.timestamp,
            Quote.price
        ).join(
            User.jobs
        ).join(
            latest_quote_subq,
            Job.id == latest_quote_subq.c.job_id
        ).join(
            Quote,
            db.and_(
                Quote.job_id == latest_quote_subq.c.job_id,
                Quote.timestamp == latest_quote_subq.c.latest_timestamp
            )
        ).filter(
            User.id == self.id
        ).all()

        # 可根据业务需求调整返回格式，这里返回字典列表方便前端渲染
        return [
            {
                "job_info": job,
                "latest_quote_time": timestamp,
                "latest_quote_price": price
            } for job, timestamp, price in query_result
        ]

如果需要展示暂无报价的关注岗位，将对应join改为outerjoin即可。

2. 原生SQL优化建议 #

你提供的原有SQL存在逻辑缺陷：GROUP BY job_id仅对timestamp做了max聚合，直接取price返回的不是最新时间对应的报价，而是同岗位下随机的报价值，结果不可靠。优化后的SQL如下：

SELECT 
    job.id, job.job_name, q.timestamp, q.price, user_jobs.user_id
FROM 
    user_jobs
INNER JOIN job ON user_jobs.job_id = job.id
INNER JOIN (
    -- 先获取每个岗位的最新报价时间
    SELECT job_id, MAX(timestamp) AS latest_ts 
    FROM quote 
    GROUP BY job_id
) AS latest ON job.id = latest.job_id
-- 二次关联报价表，拿到最新时间对应的正确报价数据
INNER JOIN quote q ON q.job_id = latest.job_id AND q.timestamp = latest.latest_ts
WHERE user_jobs.user_id = 1;

优化点：

• 修正了最新报价取值逻辑，结果准确
• 去掉无必要的外层嵌套查询，降低数据库执行开销
• 过滤条件前置，避免先查询全量数据再过滤，性能提升明显
• 给quote表添加(job_id, timestamp)联合索引后，查询效率会进一步提升

内容的提问来源于stack exchange，提问作者Dufnut