ORFU files, not tagged F5, w/redir counts

This query is marked as a draft This query has been published by Cryptic.

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;

AخA
 
SELECT CONCAT('File:', img.page_title), COUNT(rdr.page_id) AS '#/file redirect(s)'FROM categorylinks AS c1JOIN page AS img ON img.page_id = c1.cl_fromLEFT 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 = 0LEFT JOIN redirect ON rd_namespace = 6 AND rd_title = img.page_titleLEFT JOIN page AS rdr ON rdr.page_id = rd_from AND rdr.page_namespace = 6LEFT JOIN imagelinks AS il_redirect ON il_redirect.il_to = rdr.page_title AND il_redirect.il_from_namespace = 0WHERE 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 = 0GROUP BY img.page_title ASC;

By running queries you agree to the Cloud Services Terms of Use and you irrevocably agree to release your SQL under CC0 License.

Submit Query Stop Query

All SQL code is licensed under CC0 License.

SQL

Checking query status...