drop procedure if exists queryLive;
delimiter //
CREATE PROCEDURE queryLive(
IN contentType int ,
IN offset int ,
IN pageSize int ,
IN userId char(20)
)
begin
SELECT c.id AS id,
c.type,c.title,
comment.summary,
comment.time,
comment.images,
user.loginName AS authorId,
user.nickName AS authorName,
user.heat AS subscribeNum,
user.favicon AS authorIcon,
c.price,
userroles.loginName AS authorIsTeacher,
teachersubs.teacherId AS isSubscribe,
ulike.contentId AS isLike,
c.likeNum AS likeNum,
c.commentNum AS commentNum
FROM (select * from content WHERE type = 5 ORDER BY content.id DESC LIMIT offset,pagesize) as c
LEFT JOIN user ON c.userId = user.loginName
LEFT JOIN userroles ON c.userId = userroles.loginName AND userroles.roleName = 3
LEFT JOIN ulike ON c.id = ulike.contentId
AND c.userId = userId
LEFT JOIN teachersubs ON c.userId = teachersubs.teacherId AND teachersubs.userId = userId
left join new_live on c.id = new_live.liveId
LEFT JOIN comment ON comment.id = new_live.commentId;
end//
call queryLive();
