Mercurial > hgrepos > hgweb.cgi > s4

changeset 254:8a0cf46afb0a

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Revise SQL
author HIROSE Yuuji <yuuji@gentei.org>
date Mon, 18 Jul 2016 00:32:46 +0859
parents a40d258cc5aa
children 3ae8b70b8395
files s4-funcs.sh
diffstat 1 files changed, 3 insertions(+), 20 deletions(-) [+]
line wrap: on
line diff
--- a/s4-funcs.sh	Mon Jul 18 00:10:44 2016 +0859
+++ b/s4-funcs.sh	Mon Jul 18 00:32:46 2016 +0859
@@ -1495,26 +1495,9 @@
     # Display Most Recent Entry
     shortval=${dumpcollen:+"substr(val, 0, $dumpcollen)"}
     shortval=${shortval:-val}
-    DT_SQL="WITH arts AS(
-	  SELECT (SELECT rowid FROM blog WHERE id=a.blogid) brid,
-	         a.blogid, a.id id, s.val ctime
-	  FROM article a NATURAL JOIN article_s s
-	  WHERE s.key = 'ctime' AND a.author='$user'
-	  GROUP by s.id
-	)
-	SELECT a1.brid || '#' || a1.id LINK,
-	       a1.ctime,
-	       (SELECT val FROM blog_s
-	        WHERE key='title' AND id=a1.blogid) title,
-	       (SELECT gecos
-	        FROM   gecoses
-		WHERE name=(SELECT val FROM blog_s
-		            WHERE key='owner' AND id=a1.blogid)) owner
-	FROM arts a1
-	WHERE a1.ctime = (SELECT max(ctime) FROM arts WHERE blogid=a1.blogid)
-	GROUP BY a1.brid ORDER by a1.ctime DESC LIMIT 50;"
 
-    DT_SQL="SELECT b.rowid LINK,
+    # The m.aid in the next line is suspicious.  But works fine in SQLite3...
+    DT_SQL="SELECT b.rowid || '#' || m.aid LINK,
        ctime,
        (SELECT $shortval FROM blog_s WHERE key='title' AND id=b.id) title,
        (SELECT gecos FROM gecoses
@@ -1522,7 +1505,7 @@
                    WHERE key='owner' AND id=b.id)) owner
     FROM blog b
      JOIN
-     (SELECT distinct blogid, max(val) ctime
+     (SELECT distinct blogid, a.id aid, max(val) ctime
       FROM article a, article_s s
       ON a.id=s.id AND a.author='$user' AND s.key='ctime'
       GROUP BY blogid ORDER BY val DESC LIMIT 50

yatex.org