Mercurial > hgrepos > hgweb.cgi > s4
changeset 254:8a0cf46afb0a
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