Invision Community 4: SEO, prepare for v5 and dormant account notifications By Matt Monday at 02:04 PM
Apfelstrudel Posted April 21, 2021 Posted April 21, 2021 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
Solution Daniel F Posted April 21, 2021 Solution Posted April 21, 2021 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 Thomas P 1
Apfelstrudel Posted April 21, 2021 Author Posted April 21, 2021 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 Posted April 21, 2021 Posted April 21, 2021 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 Posted March 18, 2022 Author Posted March 18, 2022 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 Posted March 18, 2022 Posted March 18, 2022 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 Apfelstrudel and ArashDev 1 1
Recommended Posts