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:', page_title)
FROM categorylinks AS c1
JOIN page ON page_id = c1.cl_from
LEFT JOIN imagelinks ON il_to = page_title AND il_from_namespace = 0
LEFT JOIN categorylinks AS c2 ON c2.cl_from = c1.cl_from AND c2.cl_to = 'All_orphaned_non-free_use_Wikipedia_files'
WHERE page_namespace = 6
  AND c1.cl_to = 'All_non-free_media'
  AND il_from IS NULL
  AND c2.cl_from IS NULL;

AخA
 
SELECT CONCAT('File:', page_title)FROM categorylinks AS c1JOIN page ON page_id = c1.cl_fromLEFT JOIN imagelinks ON il_to = page_title AND il_from_namespace = 0LEFT JOIN categorylinks AS c2 ON c2.cl_from = c1.cl_from AND c2.cl_to = 'All_orphaned_non-free_use_Wikipedia_files'WHERE page_namespace = 6  AND c1.cl_to = 'All_non-free_media'  AND il_from IS NULL  AND c2.cl_from IS NULL;

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...