--- 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
 }