Top 50 Largest Uploads broken script

Contribute Bug

AI-generated summary

The “Top 50 Largest Uploads” script is broken due to a deprecated table. Ivan_Rapekas encountered an error when running the query, which uses the non-existent post_uploads table. JammyDodger explained that the correct tables to use are uploads and upload_references. A revised query was provided by JammyDodger:

SELECT posts.id AS post_id, 
    uploads.original_filename,
    ROUND(uploads.filesize / 1000000.0, 2) AS size_in_mb,
    uploads.extension,
    uploads.created_at,
    uploads.url
FROM upload_references
JOIN uploads ON uploads.id = upload_references.upload_id
JOIN posts ON posts.id = upload_references.target_id AND upload_references.target_type = 'Post'
ORDER BY uploads.filesize DESC
LIMIT 50

The issue has been fixed with a pull request (JammyDodger).

1

Sometimes I use explorer script to find large uploads.

When I try to run the query

SELECT posts.id AS post_id,
    uploads.original_filename,
    ROUND(uploads.filesize / 1000000.0, 2) AS size_in_mb,
    uploads.extension,
    uploads.created_at,
    uploads.url
FROM post_uploads
JOIN uploads ON uploads.id = post_uploads.upload_id
JOIN posts ON posts.id = post_uploads.post_id
ORDER BY uploads.filesize DESC
LIMIT 50

I get a message:

PG::UndefinedTable: ERROR:  relation "post_uploads" does not exist
LINE 13: FROM post_uploads
              ^

For those who will find this topic in the future, it is related to Search attachments by storage name

2 Likes
2

Ah yes. The post_uploads table has been deprecated. I think it needs to use the uploads and upload_references tables now?

Thanks for the report. :+1:

I think this is the updated version if you need it @Ivan_Rapekas:

SELECT posts.id AS post_id,
    uploads.original_filename,
    ROUND(uploads.filesize / 1000000.0, 2) AS size_in_mb,
    uploads.extension,
    uploads.created_at,
    uploads.url
FROM upload_references
JOIN uploads ON uploads.id = upload_references.upload_id
JOIN posts ON posts.id = upload_references.target_id AND upload_references.target_type = 'Post'
ORDER BY uploads.filesize DESC
LIMIT 50
3 Likes
4

This should be fixed by: :+1:

https://github.com/discourse/discourse-data-explorer/pull/261

2 Likes
5

This topic was automatically closed after 3 days. New replies are no longer allowed.