ORFU files, not tagged F5, w/redir counts

File pages in [[Category:All non-free media]], not in [[Category:All orphaned non-free use Wikipedia files]], and not displayed on any mainspace pages.

Seems to be a lot of them - there's only 644 pages currently in the latter cat, so there's more of these untagged than tagged.  What was that bot that tagged these again?  Has it stopped?
---
It's [[User:B-bot]].  It hasn't stopped, but it's pulling its list from [[WP:Database reports/Unused non-free files]].  And that page doesn't list files that are either
1) in use *anywhere*, not just in the main namespace, or
2) have any redirects from the file namespace, whether or not any of those are in use.

In practice, 1 isn't an issue - another bot (again, I don't remember which) removes unfree images from pages not in mainspace.  2's a big problem, though.

This query is not a suitable drop-in replacement for the one HaleBot uses; it has potential false positives for files that have two or more file redirects, the file itself is not in direct use, and at least one but not all of those redirects are in use.  (Currently, there are no such files.)  And that's also assuming that there can be a row in imagelinks for a file redirect without the file it's pointing to also having one, which doesn't seem to happen at least in practice.  In any case, see query 80422 for a version that eliminates this possibility.

Backlink: [[User talk:Legoktm#Wikipedia:Database reports/Unused non-free files]] circa [[Special:Permalink/1209291598]] on 21 February 2024.

SQL

SELECT CONCAT('File:', img.page_title), COUNT(rdr.page_id) AS '#/file redirect(s)'
FROM categorylinks AS c1
JOIN page AS img ON img.page_id = c1.cl_from
LEFT JOIN categorylinks AS c2 ON c2.cl_from = c1.cl_from AND c2.cl_to = 'All_orphaned_non-free_use_Wikipedia_files'
LEFT JOIN imagelinks AS il_direct ON il_direct.il_to = img.page_title AND il_direct.il_from_namespace = 0
LEFT JOIN redirect ON rd_namespace = 6 AND rd_title = img.page_title
LEFT JOIN page AS rdr ON rdr.page_id = rd_from AND rdr.page_namespace = 6
LEFT JOIN imagelinks AS il_redirect ON il_redirect.il_to = rdr.page_title AND il_redirect.il_from_namespace = 0
WHERE img.page_namespace = 6
  AND c1.cl_to = 'All_non-free_media'
  AND c2.cl_from IS NULL
  AND il_direct.il_from IS NULL
  AND il_redirect.il_from IS NULL
  AND img.page_is_redirect = 0
GROUP BY img.page_title ASC;

SQL

Checking query status...