s4

changeset 255:3ae8b70b8395

find changesets by author, revision, files, or words in the commit message
Add commentary about SQL
author HIROSE Yuuji <yuuji@gentei.org>
date Mon, 18 Jul 2016 09:26:12 +0859
parents 8a0cf46afb0a
children e9b33033d95f
files s4-funcs.sh
diffstat 1 files changed, 16 insertions(+), 0 deletions(-) [+]
line diff
     1.1 --- a/s4-funcs.sh	Mon Jul 18 00:32:46 2016 +0859
     1.2 +++ b/s4-funcs.sh	Mon Jul 18 09:26:12 2016 +0859
     1.3 @@ -1511,6 +1511,22 @@
     1.4        GROUP BY blogid ORDER BY val DESC LIMIT 50
     1.5       ) m
     1.6       ON b.id=m.blogid;"
     1.7 +    # This should be as follows
     1.8 +    : <<EOF
     1.9 +WITH arts AS(
    1.10 +  SELECT (SELECT rowid FROM blog WHERE id=a.blogid) brid,
    1.11 +         a.blogid, a.id id, s.val ctime
    1.12 +  FROM article a NATURAL JOIN article_s s
    1.13 +  WHERE s.key = 'ctime' AND a.author='$user'
    1.14 +  GROUP by s.id
    1.15 +)
    1.16 +SELECT a0.brid,a0.blogid,a0.id,a0.ctime
    1.17 +FROM arts a0
    1.18 +     JOIN
    1.19 +     (SELECT blogid,max(ctime) mct FROM arts a1 GROUP BY blogid) a1
    1.20 +     ON a0.blogid=a1.blogid AND a0.ctime=a1.mct
    1.21 +ORDER BY ctime DESC LIMIT 50;
    1.22 +EOF
    1.23  
    1.24      cat<<-EOF
    1.25  	<div class="fold">