Mercurial > hgrepos > hgweb.cgi > s4
changeset 343:c3311da3f059
Do not include article of the groups the user does'not belong in NEW list
author | HIROSE Yuuji <yuuji@gentei.org> |
---|---|
date | Tue, 25 Oct 2016 11:29:32 +0859 |
parents | 385b3174d265 |
children | 7ff49d1c2987 |
files | s4-funcs.sh |
diffstat | 1 files changed, 61 insertions(+), 28 deletions(-) [+] |
line wrap: on
line diff
--- a/s4-funcs.sh Mon Oct 24 09:47:24 2016 +0859 +++ b/s4-funcs.sh Tue Oct 25 11:29:32 2016 +0859 @@ -1448,39 +1448,72 @@ htmlheader=$layout/html.m4.html showgroup "$grid" } -listnewblogsql() { # $1=user +sql4interestblogs() { cat<<EOF -`sql4readableblogs` -with article_ctime as ( - select id,blogid,author,val ctime - from article join article_s s using(id) - where s.key='ctime' +CREATE TEMPORARY VIEW interestblogs AS + SELECT blog.rowid rid, id, author + FROM blog + NATURAL JOIN + (SELECT id, val owner FROM blog_s WHERE key='owner') bs + WHERE CASE WHEN (SELECT name FROM user where name=bs.owner) IS NOT NULL + THEN 1 -- blog owner is an user, READABLE + WHEN (SELECT user FROM grp_mem + WHERE gname=bs.owner AND user='$user') IS NULL + THEN 0 + ELSE 1 + END; +EOF +} +listnewblogsql() { # $1=user + deftime=`query "SELECT coalesce((SELECT time FROM acclog_user + WHERE tblrowid=$blogreadflagrowid), "0");"` + cat<<EOF # | tee tmp/sql.out +`sql4interestblogs` +WITH article_ctime as ( + SELECT id,blogid,author,val ctime + FROM article join article_s s using(id) + WHERE s.key='ctime' ), blog_title_owner as ( - select blg.rid brid, id, + SELECT blg.rid brid, id, max(case key when 'title' then val end) title, max(case key when 'owner' then val end) owner - from readableblogs blg, blog_s using(id) group by id -), acclog_user as ( - select * from acclog where user='$user' and tbl='blog' + FROM interestblogs blg, blog_s using(id) group by id +), visited AS ( + SELECT b.id id, brid, b.owner owner, b.title title, ctime, ac.author author + FROM blog_title_owner b, article_ctime ac + JOIN acclog al + ON b.id=ac.blogid + AND al.tbl='blog' + AND al.tblrowid=brid + AND al.user='$user' + AND al.time < ctime + AND '$deftime' < ctime +), unvisited as ( + SELECT b.id id, brid, b.owner owner, b.title title, ctime, ac.author author + FROM blog_title_owner b, article_ctime ac + ON b.id=ac.blogid + WHERE brid NOT IN (SELECT tblrowid FROM acclog + WHERE tbl='blog' AND user='$user') + AND ctime > '$deftime' ), blogall as ( - select b.id, brid, b.owner, b.title, ctime, ac.author - from blog_title_owner b, article_ctime ac - on b.id=ac.blogid -) - select brid LINK, - 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.brid=round(l.tblrowid) - group by bl.id - having "新着" > 0 order by ctime desc, "新着" desc, bl.id - limit 10; + /* --------------------------------------- + Collect new articles with dividing them into visited and unvisited + separately, because constructing joined table of artice X acclog + tends to become HUGE combinations. + --------------------------------------- */ + SELECT * FROM visited UNION SELECT * FROM unvisited +), news as ( + select bl.brid brid, bl.title, bl.id blid, ctime, + count(bl.id) "新着", bl.owner, bl.author + from blogall bl /* left join a_u l + on bl.brid=l.tblrowid */ + group by bl.id order by ctime desc,"新着" desc, bl.id + LIMIT 10 +) SELECT brid LINK, "新着", + (SELECT count(*) FROM article WHERE blogid=blid) "総数", + ctime, title, + (SELECT gecos FROM gecoses WHERE name=author) gecos + FROM news; EOF }