105.SQLAlchemy数据的排序

from random import randint
from turtle import title

from sqlalchemy import Column, String, Integer, ForeignKey
from sqlalchemy.orm import relationship, backref
from util_db import Base, Session

class User(Base):
    __tablename__ = 't_user'
    id = Column(Integer, primary_key = True, autoincrement=True)
    name = Column(String(32))
    age = Column(Integer)

    def __repr__(self) -> str:
        return f'<User: id={self.id} name = {self.name} age = {self.age}>'

class News(Base):
    __tablename__ = 't_news'
    id = Column(Integer, primary_key = True, autoincrement=True)
    title = Column(String(32), nullable = False)
    content = Column(String(32), nullable = False)
    read_count = Column(Integer)
    uid = Column(Integer, ForeignKey('t_user.id'))
    # 若想对子表（存在外键）进行排序，就必须得通过user，所以得添加backref
    user = relationship('User', backref = backref('newss', order_by = read_count))
    # user = relationship('User', backref = 'newss')

    def __repr__(self) -> str:
        return f'<News: id={self.id} title={self.title} content={self.content} read_count={self.read_count}>'
    
def create_user():
    with Session() as ses:
        for i in range(10):
            user = User(name = f'用户{i}', age = randint(6,30))
            ses.add(user)
        for i in range(10):
            news = News(title = f'新闻:{i}',content = f'content:{i}',read_count=randint(0,1000))
            user.newss.append(news)
        ses.commit()

def query_user():
    with Session() as ses:
        # 常规输出
        # user = ses.query(User).all()

        # 升序输出                   根据某表的某字段
        # user = ses.query(User).order_by(User.age).all()

        # 降序输出
        user = ses.query(User).order_by(User.age.desc()).all()

        for i in user:
            print(i)

def query_new():
    with Session() as ses:
        # 从子表中查询
        new = ses.query(News).order_by(News.read_count).all()
        # 从主表查子表的方法：先找到主表的最后一条，通过节点newss去查
        user = ses.query(User).all()
        news = user[-1].newss
        # print(user)
        # print(news)
        for i in news:
            print(i)
        print("----------------------------------------")
        for i in new:
            print(i)

if __name__ == "__main__":
    # Base.metadata.drop_all()
    # Base.metadata.create_all()
    # create_user()
    # query_user()
    query_new()