How can I find topics with the most attachments?

[Apfelstrudel]

Hi community,

I'd like to minimize pointless attachments in my community.

So how can I find topics with the most attachments in a forum or subforum?

Best regards

[Daniel F]

select id1 as topic_id ,count(*) as total FROM core_attachments_map where location_key = 'forums_Forums'  group by id1 order by total desc

will return the topics with the most attachments

[Apfelstrudel]

Thanks Daniel.

Does it make sense to delete some topics right now, or are we still having the bug that attachments are not physically deleted?

[Daniel F]

Why would you want to delete all the content just because of few attachments?:)

Is it really worth to delete some valuable content to get rid of the attachments? I would check the topics manually and probably remove only the attachments!

2. There's no bug in topics with attachment deletion, there's only one affecting Pages records;)

3. Are you worried about the amount of attachments or their size? You could also remove only the really big ones ( you can locate them in your ACP => System => Files ; and sort the table by the size)

[Apfelstrudel]

On 4/21/2021 at 8:25 AM, Daniel F said:

select id1 as topic_id ,count(*) as total FROM core_attachments_map where location_key = 'forums_Forums'  group by id1 order by total desc

will return the topics with the most attachments

Hello Daniel,

this query works pretty good but what do I have to add to this query that it only shows results for a specific forum id?

[Marc]

2 hours ago, Apfelstrudel said:

Hello Daniel,

this query works pretty good but what do I have to add to this query that it only shows results for a specific forum id?

Please note. We cannot assist with custom queries. However you would need to join the topic table, which would then give you the forum ID. Something like this would give you the forum ID so you can then just add a where clause to limit as needed

select a.id1 as topic_id,t.forum_id,count(*) as total FROM core_attachments_map a inner join forums_topics t on t.tid = a.id1 where a.location_key = 'forums_Forums' group by a.id1 order by total desc