s4
changeset 435:9f7b97c8b694
Group member listing with the number of POST fasten much
| author | HIROSE Yuuji <yuuji@gentei.org> |
|---|---|
| date | Fri, 23 Jun 2017 23:23:40 +0859 |
| parents | cf20459261cb |
| children | e1bdad674c09 |
| files | s4-funcs.sh |
| diffstat | 1 files changed, 37 insertions(+), 1 deletions(-) [+] |
line diff
1.1 --- a/s4-funcs.sh Fri Jun 23 23:11:31 2017 +0859 1.2 +++ b/s4-funcs.sh Fri Jun 23 23:23:40 2017 +0859 1.3 @@ -2389,7 +2389,7 @@ 1.4 fi 1.5 # New entry 1.6 sql="select /* Ahh, ugly SQL, I wanna fix... */ 1.7 - case 1.8 + case /* YES, can be REMOVED if it works until 2017-07-01 */ 1.9 when (select user from grp_adm where 1.10 gname=(select gname from grp where rowid=$grid) 1.11 and user=a.name) is not null 1.12 @@ -2413,6 +2413,42 @@ 1.13 WHERE name in (select user from grp_mem where 1.14 gname=(select gname from grp where rowid=$grid)) 1.15 ORDER by a.gecos;" 1.16 + sql="WITH mems AS ( 1.17 + SELECT g.rowid, name, gecos FROM grp_mem gm LEFT JOIN gecoses g 1.18 + ON gm.user=g.name 1.19 + WHERE gname=(SELECT gname FROM grp WHERE rowid=$grid) 1.20 + ), posts AS ( 1.21 + SELECT author, count(author) post 1.22 + FROM article NATURAL JOIN article_s 1.23 + WHERE blogid IN (SELECT id FROM blog_s 1.24 + WHERE key='owner' 1.25 + AND val=(SELECT gname FROM grp WHERE rowid=$grid)) 1.26 + AND key='text' 1.27 + GROUP BY author 1.28 + ), teams AS ( 1.29 + SELECT user, group_concat(val, ', ') team 1.30 + FROM grp_mem_m 1.31 + WHERE gname=(SELECT gname FROM grp WHERE rowid=$grid) 1.32 + AND key='team' 1.33 + GROUP BY user 1.34 + ), user_post AS ( 1.35 + SELECT m.rowid, name, m.gecos, coalesce(post, 0) as POST 1.36 + FROM mems m LEFT JOIN posts 1.37 + ON m.name=posts.author 1.38 + GROUP by m.rowid 1.39 + ) 1.40 + SELECT 1.41 + CASE 1.42 + WHEN (SELECT user FROM grp_adm 1.43 + WHERE gname=(SELECT gname FROM grp WHERE rowid=$grid) 1.44 + AND user=up.name) IS NOT NULL 1.45 + then 'k' 1.46 + ELSE '' 1.47 + END || rowid || ',' || gecos NAME, 1.48 + post POST, team TEAM 1.49 + FROM user_post up LEFT JOIN teams t 1.50 + ON up.name=t.user 1.51 + ORDER BY gecos;" 1.52 ## err grpaction: "`echo \"$sql\"`" 1.53 tf=$tmpd/title.$$ 1.54 echo "グループ[<a href=\"?grp+$grid\">$grp</a>]参加メンバーに対する操作" > $tf