query2.sql

SELECT
  blob.rid AS rid,
  uuid,
  datetime(event.mtime,toLocal()) AS mDateTime,
  coalesce(ecomment,comment)
    || ' (user: ' || coalesce(euser,user,'?')
    || (SELECT case when length(x)>0 then ' tags: ' || x else '' end
          FROM (SELECT group_concat(substr(tagname,5), ', ') AS x
                  FROM tag, tagxref
                 WHERE tagname GLOB 'sym-*' AND tag.tagid=tagxref.tagid
                   AND tagxref.rid=blob.rid AND tagxref.tagtype>0))
    || ')' as comment,
  (SELECT count(*) FROM plink WHERE pid=blob.rid AND isprim)
       AS primPlinkCount,
  (SELECT count(*) FROM plink WHERE cid=blob.rid) AS plinkCount,
  event.mtime AS mtime,
  tagxref.value AS branch
FROM tag CROSS JOIN event CROSS JOIN blob
     LEFT JOIN tagxref ON tagxref.tagid=tag.tagid
  AND tagxref.tagtype>0
  AND tagxref.rid=blob.rid
WHERE blob.rid=event.objid
  AND tag.tagname='branch'
  AND event.mtime >= julianday('2016-09-02T00:00:00');