Skip to content

Instantly share code, notes, and snippets.

@vladdancer
Last active June 20, 2023 13:52
Show Gist options
  • Save vladdancer/80f2861c26dd1e36054f2b4822f25195 to your computer and use it in GitHub Desktop.
Save vladdancer/80f2861c26dd1e36054f2b4822f25195 to your computer and use it in GitHub Desktop.
Get stats about drupal files/commerce orders in database
# All image files
SELECT
floor(filesize/1000000)*1000000 as bucket,
SUM(`filesize`) as bytes,
(SUM(`filesize`) / 1024 / 1024 / 1024 ) AS GB,
COUNT(*) AS COUNT,
RPAD('', LN(COUNT(*)), '*') AS bar
FROM file_managed
WHERE (`filemime` LIKE 'image%') AND (`status` = '1')
GROUP BY bucket
ORDER BY bar DESC
# Only used files
SELECT
floor(filesize/1000000)*1000000 as bucket,
SUM(`filesize`) as bytes,
(SUM(`filesize`) / 1024 / 1024 / 1024 ) AS GB,
COUNT(*) AS COUNT,
RPAD('', LN(COUNT(*)), '*') AS bar
FROM file_managed
WHERE (`filemime` LIKE 'image%') AND (`status` = '1')
AND fid NOT IN (SELECT DISTINCT(fid) FROM file_usage)
GROUP BY bucket
ORDER BY bar DESC
SELECT
MONTH(FROM_UNIXTIME(created)) as MONTH,
YEAR(FROM_UNIXTIME(created)) as YEAR,
COUNT(*),
RPAD('', LN(COUNT(*)), '*') AS bar
FROM commerce_order
GROUP BY 1,2
ORDER BY YEAR DESC, MONTH ASC
SELECT
MONTH(FROM_UNIXTIME(created)) as MONTH,
YEAR(FROM_UNIXTIME(created)) as YEAR,
type,
COUNT(*),
RPAD('', LN(COUNT(*)), '*') AS bar
FROM node
GROUP BY 1,2,3
ORDER BY YEAR DESC, MONTH ASC
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment