Memories tags SQL query

tags.sql

WITH RECURSIVE oc_cte_folders_all(fileid, name, hidden) AS (
  SELECT 
    f.fileid, 
    f.name, 
    (0) AS hidden 
  FROM 
    oc_filecache f 
  WHERE 
    (
      f.fileid IN ('56') 
      AND NOT EXISTS (
        SELECT 
          1 
        FROM 
          oc_filecache f2 
        WHERE 
          (f2.parent = f.fileid) 
          AND (
            f2.name = '.nomedia' 
            OR f2.name = '.nomemories'
          )
      )
    ) 
  UNION ALL 
  SELECT 
    f.fileid, 
    f.name, 
    (
      CASE WHEN c.hidden = 1 
      OR f.name LIKE '.%' THEN 1 ELSE 0 END
    ) AS hidden 
  FROM 
    oc_filecache f 
    INNER JOIN oc_cte_folders_all c ON (
      f.parent = c.fileid 
      AND f.mimetype = (
        SELECT 
          MAX(id) 
        FROM 
          oc_mimetypes 
        WHERE 
          mimetype = 'httpd/unix-directory'
      ) 
      AND (f.name NOT LIKE '.%')
    ) 
  WHERE 
    (
      NOT EXISTS (
        SELECT 
          1 
        FROM 
          oc_filecache f2 
        WHERE 
          (f2.parent = f.fileid) 
          AND (
            f2.name = '.nomedia' 
            OR f2.name = '.nomemories'
          )
      )
    )
), 
oc_cte_folders AS (
  SELECT 
    fileid, 
    (0) AS hidden 
  FROM 
    oc_cte_folders_all 
  GROUP BY 
    fileid
) 
SELECT 
  `st`.`id`, 
  `st`.`name`, 
  COUNT(DISTINCT m.fileid) AS `count`, 
  MAX(m_cov_cover.objectid) AS `cover`, 
  MAX(m_cov_cover_f.etag) AS `cover_etag` 
FROM 
  `oc_systemtag` `st` 
  INNER JOIN `oc_systemtag_object_mapping` `stom` ON (`stom`.`objecttype` = 'files') 
  AND (`stom`.`systemtagid` = `st`.`id`) 
  LEFT JOIN `oc_memories_covers` `m_cov_cover` ON (`m_cov_cover`.`uid` = 'admin') 
  AND (
    `m_cov_cover`.`clustertype` = 'tags'
  ) 
  AND (
    `m_cov_cover`.`clusterid` = `st`.`id`
  ) 
  AND (
    EXISTS (
      SELECT 
        '1' 
      FROM 
        `oc_systemtag_object_mapping` `cov_objs` 
      WHERE 
        (
          `cov_objs`.`objectid` = `m_cov_cover`.`objectid`
        ) 
        AND (
          `cov_objs`.`systemtagid` = `st`.`id`
        )
    )
  ) 
  AND (
    EXISTS (
      SELECT 
        '1' 
      FROM 
        `oc_filecache` `cov_f` 
        INNER JOIN `oc_cte_folders` `cov_cte_f` ON (
          `cov_cte_f`.`fileid` = `cov_f`.`parent`
        ) 
        AND (`cov_cte_f`.`hidden` = '0') 
      WHERE 
        `cov_f`.`fileid` = `m_cov_cover`.`fileid`
    )
  ) 
  INNER JOIN `oc_memories` `m` ON `m`.`objectid` = `stom`.`objectid` 
  INNER JOIN `oc_filecache` `f` ON `f`.`fileid` = `m`.`fileid` 
  INNER JOIN `oc_cte_folders` `cte_f` ON `f`.`parent` = `cte_f`.`fileid` 
  LEFT JOIN `oc_filecache` `m_cov_cover_f` ON `m_cov_cover_f`.`fileid` = `m_cov_cover`.`fileid` 
WHERE 
  `st`.`visibility` = '1' 
GROUP BY 
  `st`.`id` 
ORDER BY 
  LOWER(st.name) ASC, 
  `st`.`id` ASC