109.SQLAlchemy中join的使用

from random import randint
from sqlalchemy import Column, Integer, String,ForeignKey,func
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):
        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'))
    uid = Column(Integer) # 测试2：不去建立外键和不去关联
    # user = relationship('User', backref = 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_data():
    Base.metadata.drop_all()
    Base.metadata.create_all()
    with Session() as ses:
        for i in range(2):
            user = User(name=f'name{i}', age = randint(6,30))
            ses.add(user)
        for i in range(10):
            news = News(title=f'title{i}',content=f'info{i}',read_count=randint(0,1000),uid = randint(1,2))
            # 没有级联操作了，单独提交
            ses.add(news)
        ses.commit()

def query_join():
    # 找到所有用户 新闻数量， 按照发表新闻数量进行排序
    '''
SELECT u.name, count(n.id) news中的id是新闻的编号，uid才是作者user的编号
FROM t_news n JOIN t_user u 
ON n.uid = u.id
GROUP BY u.id
ORDER BY count(n.id)
    '''
    with Session() as ses:
        # join里面放对象类名时，默认以外键为关联条件
        rs = ses.query(User.name, func.count(News.id)).join(News).group_by(User.id).order_by(News.id).all()
        print(rs)

def query_join_byself():
    # 自己手动去指定关联关系
    with Session() as ses:
        rs = ses.query(User.name, func.count(News.id)).join(News, News.uid== User.id).group_by(User.id).order_by(News.id).all()
        print(rs)




if __name__ == "__main__":
    create_data()
    # query_join()
    query_join_byself()