背景:最近在开发一个flask项目,仿知乎的系统,数据库链接采用sqlalchemy
首页的需求:获取最多赞同和最多评论的文章,排在前面
import math
from flask import Blueprint, render_template, request, abort, flash, redirect, url_for, jsonify
from flask_login import login_required, current_user
from sqlalchemy import func, select, case, desc
from sqlalchemy.orm import aliased
from models import Question, Answer, AnswerComment, db, AnswerLove
@qa.route('/test/')
def test():
# 首页
# 获取赞同的问题id ,with_entities:指定返回的字段
answerlove_qids = [list(q_id) for q_id in AnswerLove.query.with_entities(AnswerLove.q_id).all()]
# 获取回复评论问题id(即有评论的问题)
answer_qids = [list(q_id) for q_id in Answer.query.with_entities(Answer.q_id).filter(Answer.is_valid == 1).all()]
'''左链接查询到问题的id、title、img、desc_、content、get_img_url、love_count(赞同的数量)'''
question_result = (Question.query.outerjoin(AnswerLove, AnswerLove.q_id == Question.id). \
with_entities( # 限制返回的字段
Question.id.label('id'), # label:别名
Question.title.label('title'),
Question.img.label('img')