|
|
from sqlalchemy import func
|
|
|
from .user import Users
|
|
|
from main import db
|
|
|
|
|
|
|
|
|
class Article(db.Model):
|
|
|
__tablename__ = 'article'
|
|
|
|
|
|
# 加入数据库中的所有列
|
|
|
articleid = db.Column(db.String(64), primary_key=True)
|
|
|
userid = db.Column(db.String(64), db.ForeignKey('user.userid'))
|
|
|
headline = db.Column(db.String(255), nullable=False)
|
|
|
content = db.Column(db.String(255), nullable=False)
|
|
|
type = db.Column(db.Integer, nullable=False) # 通常使用 db.Integer 而不是 db.int(255)
|
|
|
readcount = db.Column(db.Integer, nullable=False)
|
|
|
recommended = db.Column(db.Integer, nullable=False)
|
|
|
credit = db.Column(db.Integer, nullable=False)
|
|
|
hidden = db.Column(db.Integer, nullable=False)
|
|
|
drafted = db.Column(db.Integer, nullable=False)
|
|
|
checked = db.Column(db.Integer, nullable=False)
|
|
|
replycount = db.Column(db.Integer, nullable=False) # 评论数量
|
|
|
createtime = db.Column(db.DateTime, nullable=False) # 使用 DateTime 类型，并设置默认值为当前 UTC 时间
|
|
|
updatetime = db.Column(db.DateTime, nullable=False)
|
|
|
|
|
|
# 可以添加其他字段，如 title, content 等
|
|
|
|
|
|
# 关联 Users 表
|
|
|
user = db.relationship('Users', backref=db.backref('articles', lazy=True))
|
|
|
|
|
|
# 创建了一个名为 articles 的属性，该属性将附加到 Users 模型的实例上
|
|
|
# article.user可以直接用
|
|
|
|
|
|
# 查询所有文章
|
|
|
@classmethod
|
|
|
def find_all(cls):
|
|
|
options = db.joinedload(cls.user)
|
|
|
return cls.query.options(options).all()
|
|
|
|
|
|
# 根据id查询文章
|
|
|
@classmethod
|
|
|
def find_by_id(cls, articleid):
|
|
|
# 因为每一篇文章要使用到用户的内容信息，所以要关联到用户信息表user
|
|
|
options = db.joinedload(cls.user)
|
|
|
return cls.query.options(options).get(articleid)
|
|
|
|
|
|
|
|
|
# Ai 给的东西
|
|
|
#----------------------------------------------------------
|
|
|
@classmethod
|
|
|
def find_max_less_than_id(cls, articleid):
|
|
|
# 首先找到小于给定articleid的最大articleid
|
|
|
max_id = cls.query.filter(cls.articleid < articleid).order_by(cls.articleid.desc()).first()
|
|
|
if max_id is None:
|
|
|
return None # 如果没有找到任何小于给定articleid的文章，则返回None
|
|
|
|
|
|
# 然后使用这个最大的articleid来查询文章，并关联用户信息
|
|
|
options = db.joinedload(cls.user)
|
|
|
max_article = cls.query.options(options).get(max_id.articleid)
|
|
|
|
|
|
return max_article
|
|
|
|
|
|
@classmethod
|
|
|
def find_min_greater_than_id(cls, articleid):
|
|
|
# 首先找到大于给定articleid的最小articleid
|
|
|
min_id_query = cls.query.filter(cls.articleid > articleid).order_by(cls.articleid.asc()).limit(1)
|
|
|
min_id_result = min_id_query.first()
|
|
|
if min_id_result is None:
|
|
|
return None # 如果没有找到任何大于给定articleid的文章，则返回None
|
|
|
|
|
|
# 提取出最小的articleid
|
|
|
min_id = min_id_result.articleid
|
|
|
|
|
|
# 然后使用这个最小的articleid来查询文章，并关联用户信息
|
|
|
options = db.joinedload(cls.user)
|
|
|
min_article = cls.query.options(options).get(min_id)
|
|
|
|
|
|
return min_article
|
|
|
#-------------------------------------------------------
|
|
|
|
|
|
# 分页查询
|
|
|
@classmethod
|
|
|
def find_limit_with_user(cls, offset, limit):
|
|
|
'''
|
|
|
自定义每一页的数量
|
|
|
:param offset: 开始的位置
|
|
|
:param limit: 结束的位置
|
|
|
:return: 查询到的每一页的类
|
|
|
'''
|
|
|
# 加载options
|
|
|
options = db.joinedload(cls.user)
|
|
|
return cls.query.options(options).filter_by(drafted=0).order_by(Article.articleid.desc()).limit(limit).offset(
|
|
|
offset).all()
|
|
|
|
|
|
# 统计当前文章的数量
|
|
|
@classmethod
|
|
|
def get_total_count(cls):
|
|
|
return cls.query.count()
|
|
|
|
|
|
# 根据文章类型获得文章
|
|
|
@classmethod
|
|
|
def find_by_type(cls, type, offset, limit):
|
|
|
return cls.query.filter_by(type=type).order_by(Article.articleid.desc()).limit(limit).offset(offset).all()
|
|
|
|
|
|
# 根据文章类型获取总数量
|
|
|
@classmethod
|
|
|
def get_total_count_by_type(cls, type):
|
|
|
return cls.query.filter_by(type=type).count()
|
|
|
|
|
|
# 根据文章标题进行模糊搜索
|
|
|
@classmethod
|
|
|
def find_by_headline(cls, headline, offset, limit):
|
|
|
return cls.query.filter(Article.headline.like('%' + headline + '%')).limit(limit).offset(offset).all()
|
|
|
|
|
|
# 统计模糊搜索的总数量
|
|
|
@classmethod
|
|
|
def get_total_count_by_like_headline(cls, headline):
|
|
|
return cls.query.filter(Article.headline.like('%' + headline + '%')).count()
|
|
|
|
|
|
# 最新文章
|
|
|
@classmethod
|
|
|
def side_new(cls):
|
|
|
return cls.query.filter().order_by(Article.articleid.desc()).limit(9).all()
|
|
|
|
|
|
# 特别推荐
|
|
|
@classmethod
|
|
|
def side_recommend(cls):
|
|
|
return cls.query.filter(Article.recommended == 1).order_by(func.rand()).limit(9).all() # func.rand()随机抽取
|
|
|
|
|
|
# 最多阅读
|
|
|
@classmethod
|
|
|
def side_most(cls):
|
|
|
return cls.query.filter().order_by(Article.readcount.desc()).limit(9).all()
|
|
|
|
|
|
# 一次性返回三个数据
|
|
|
@classmethod
|
|
|
def side_method(cls):
|
|
|
return cls.side_new(), cls.side_most(), cls.side_recommend()
|
|
|
|
|
|
# 每阅读一次，阅读次数加一
|
|
|
@classmethod
|
|
|
def update_readcount(cls, articleid):
|
|
|
# 使用SQLAlchemy的update方法直接在数据库层面更新，避免竞态条件
|
|
|
cls.query.filter_by(articleid=articleid).update({cls.readcount: cls.readcount + 1})
|
|
|
db.session.commit()
|
|
|
|
|
|
|
|
|
# 评论数量加一
|
|
|
@classmethod
|
|
|
def replaycount_add(cls,articleid):
|
|
|
cls.query.filter_by(articleid=articleid).update({cls.replycount: cls.replycount + 1})
|
|
|
db.session.commit()
|
|
|
|
|
|
|
|
|
# 后台管理部分
|
|
|
# 查询article表中除草稿外的所有数据并返回结果集
|
|
|
@classmethod
|
|
|
def find_all_except_draft(cls, start, count):
|
|
|
result = cls.query.filter(Article.drafted == 0).order_by(
|
|
|
Article.articleid.desc()).limit(count).offset(start).all()
|
|
|
return result
|
|
|
|
|
|
@classmethod
|
|
|
def get_count_except_draft(cls):
|
|
|
count = cls.query.filter(Article.drafted == 0).count()
|
|
|
return count
|
|
|
|
|
|
@classmethod
|
|
|
def find_by_type_except_draft(cls, start, count, type):
|
|
|
if type == 0:
|
|
|
result = cls.find_all_except_draft(start, count)
|
|
|
total = cls.get_count_except_draft()
|
|
|
else:
|
|
|
result = cls.query.filter(Article.drafted == 0,
|
|
|
Article.type == type).order_by(Article.articleid.desc())\
|
|
|
.limit(count).offset(start).all()
|
|
|
total = cls.query.filter(Article.drafted == 0,
|
|
|
Article.type == type).count()
|
|
|
return result, total # 返回分页结果集和不分页的总数量
|
|
|
|
|
|
@classmethod
|
|
|
def find_by_headline_except_draft(cls, headline):
|
|
|
result = cls.query.filter(Article.headline.like('%' + headline + '%'))\
|
|
|
.order_by(Article.articleid.desc()).all()
|
|
|
return result
|
|
|
|
|
|
@classmethod
|
|
|
def switch_hidden(cls, articleid):
|
|
|
print("articleid=", articleid)
|
|
|
row = cls.query.filter_by(articleid=articleid).first()
|
|
|
if row.hidden == 1:
|
|
|
row.hidden = 0
|
|
|
else:
|
|
|
row.hidden = 1
|
|
|
db.session.commit()
|
|
|
print("hidden=", row.hidden)
|
|
|
return row.hidden # 将当前最新状态返回给控制层
|
|
|
|
|
|
@classmethod
|
|
|
def replaycount_add(cls, articleid):
|
|
|
cls.query.filter_by(articleid=articleid).update({cls.replycount: cls.replycount + 1})
|
|
|
db.session.commit()
|
|
|
|
|
|
@classmethod
|
|
|
def switch_recommended(cls, articleid):
|
|
|
row = cls.query.filter_by(articleid=articleid).first()
|
|
|
if row.recommended == 1:
|
|
|
cls.query.filter_by(articleid=articleid).update({cls.recommended: cls.recommended - 1})
|
|
|
else:
|
|
|
cls.query.filter_by(articleid=articleid).update({cls.recommended: cls.recommended + 1})
|
|
|
db.session.commit()
|
|
|
return row.recommended
|
|
|
|
|
|
@classmethod
|
|
|
def switch_checked(cls, articleid):
|
|
|
print("数据库中articleid=",articleid)
|
|
|
row = cls.query.filter_by(articleid=articleid).first()
|
|
|
print("数据库中row.checked=",row.checked)
|
|
|
if row.checked == 1:
|
|
|
row.checked = 0
|
|
|
else:
|
|
|
row.checked = 1
|
|
|
db.session.commit()
|
|
|
return row.checked
|
|
|
|
|
|
# 删除文章信息
|
|
|
@classmethod
|
|
|
def do_deletesign(cls, articleid):
|
|
|
result = cls.query.filter_by(articleid=articleid).first()
|
|
|
if result:
|
|
|
db.session.delete(result)
|
|
|
db.session.commit() |
