Mercurial > hgrepos > hgweb.cgi > s4
changeset 242:7dd85c224bbb
Fix NEWS sql
author | HIROSE Yuuji <yuuji@gentei.org> |
---|---|
date | Fri, 08 Jul 2016 19:26:09 +0859 |
parents | 0c817d4a549a |
children | 466ea3e4b039 |
files | s4-funcs.sh |
diffstat | 1 files changed, 8 insertions(+), 15 deletions(-) [+] |
line wrap: on
line diff
--- a/s4-funcs.sh Thu Jun 30 17:55:35 2016 +0859 +++ b/s4-funcs.sh Fri Jul 08 19:26:09 2016 +0859 @@ -1388,38 +1388,31 @@ max(case key when 'owner' then val end) owner from blog, blog_s using(id) group by id ), acclog_user as ( - -- select * from acclog where user='$user' and tbl='blog' - -- GET (user,tbl,tblrowid,max(time,DEFAULTtime)) - SELECT user, tbl, tblrowid, - max(time, - coalesce((SELECT time FROM acclog - WHERE user='$user' and tbl='blog' - AND tblrowid=$blogreadflagrowid), " ")) time - FROM acclog - WHERE user='$user' AND tbl='blog' + select * from acclog where user='$user' and tbl='blog' ), blogall as ( select b.id, b.rowid, b.owner, b.title, ctime, ac.author from blog_title_owner b, article_ctime ac on b.id=ac.blogid ) select rowid LINK, - sum(case when l.time is not null - then l.time < ctime - else 1 - end) "新着", + sum(max(coalesce(l.time, "0"), + coalesce((SELECT time FROM acclog_user + WHERE tblrowid=$blogreadflagrowid), "0")) + < ctime + ) "新着", count(id) "総数", ctime, title, /* owner gecos */ (select gecos from gecoses where name=bl.owner) gecos from blogall bl left join acclog_user l on bl.rowid=round(l.tblrowid) where case when (select name from user where owner=name) is not null - then 1 /* user's diary is always readable */ + then 1 /* user's diary is always readable */ when (select val from grp_s where gname=owner and key='regmode') = 'moderated' and -- moderated group and nonmember can't read, not counting (select user from grp_mem where gname=owner and user='$user') is null - then 0 + then 0 else 1 end group by bl.id