Support to Extract a Report using Data Explorer Plugin

Support
,
1

Dear All,

I need your support to execute an SQL code to view the sizes of each category to follow up periodically to ensure that the category is not exceeding the agreed size by the moderators.

We executed the following code but the result is displayed in bytes and we need it to be displayed in GB or MB.

-- Query to estimate the data size for each category in MB and GB
SELECT 
    c.id AS category_id,
    c.name AS category_name,
    COUNT(DISTINCT t.id) AS topics_count,
    COUNT(p.id) AS posts_count,
    ROUND(SUM(LENGTH(p.raw)) / 1024 / 1024, 2) AS total_text_size_mb,
    ROUND(SUM(LENGTH(p.raw)) / 1024 / 1024 / 1024, 4) AS total_text_size_gb,
    ROUND(COALESCE(SUM(u.filesize), 0) / 1024 / 1024, 2) AS total_uploads_size_mb,
    ROUND(COALESCE(SUM(u.filesize), 0) / 1024 / 1024 / 1024, 4) AS total_uploads_size_gb,
    ROUND((COALESCE(SUM(u.filesize), 0) + SUM(LENGTH(p.raw))) / 1024 / 1024, 2) AS total_category_size_mb,
    ROUND((COALESCE(SUM(u.filesize), 0) + SUM(LENGTH(p.raw))) / 1024 / 1024 / 1024, 4) AS total_category_size_gb
FROM 
    categories c
LEFT JOIN 
    topics t ON t.category_id = c.id
LEFT JOIN 
    posts p ON p.topic_id = t.id
LEFT JOIN 
    post_custom_fields pcf ON pcf.post_id = p.id AND pcf.name = 'uploads'
LEFT JOIN 
    uploads u ON u.id = pcf.value::int
WHERE 
    c.id > 0 -- Exclude system categories
    AND t.deleted_at IS NULL -- Exclude deleted topics
    AND p.deleted_at IS NULL -- Exclude deleted posts
GROUP BY 
    c.id, c.name
ORDER BY 
    total_category_size_mb DESC

Need your support to display it in the following format:

image
image1892×296 5.58 KB

2

I think you just need to change the calcs to reflect your units. for example, for GB

-- Query to estimate the data size for each category in GB
SELECT 
    c.id AS category_id,
    c.name AS category_name,
    COUNT(DISTINCT t.id) AS topics_count,
    COUNT(p.id) AS posts_count,
    ROUND(SUM(LENGTH(p.raw)) / 1024 / 1024 / 1024, 4) AS total_text_size_gb,
    ROUND(COALESCE(SUM(u.filesize), 0) / 1024 / 1024 / 1024, 4) AS total_uploads_size_gb,
    ROUND((COALESCE(SUM(u.filesize), 0) + SUM(LENGTH(p.raw))) / 1024 / 1024 / 1024, 4) AS total_category_size_gb
FROM 
    categories c
LEFT JOIN 
    topics t ON t.category_id = c.id
LEFT JOIN 
    posts p ON p.topic_id = t.id
LEFT JOIN 
    post_custom_fields pcf ON pcf.post_id = p.id AND pcf.name = 'uploads'
LEFT JOIN 
    uploads u ON u.id = pcf.value::int
WHERE 
    c.id > 0 -- Exclude system categories
    AND t.deleted_at IS NULL -- Exclude deleted topics
    AND p.deleted_at IS NULL -- Exclude deleted posts
GROUP BY 
    c.id, c.name
ORDER BY 
    total_category_size_gb DESC
1 Like
3

Noted, thank you !

4

Very 101 level question… mine is showing practically zero sizes. Will that come from S3 use or isn’t images counted as an upload? Sure, those are relatively small, as are some PDFs and e-books, but still those should be bigger than 0.001 GB.