I'm trying to get a list of all external links in our wiki
(version 1.43.3, with a PostgreSQL DB v. 15.14).
The links are in the externallinks table, split into the
"el_to_domain_index" and "el_to_path" fields.
But the el_to_domain_index column is reversed, so this doesn't work:
SELECT page_title, el_to_domain_index, el_to_path, CONCAT(el_to_domain_index, el_to_path) AS bad_link
FROM page
JOIN externallinks ON page.page_id = externallinks.el_id
WHERE page_is_redirect=0
AND page_namespace=0
AND el_to_domain_index LIKE 'http%'
ORDER BY page_title;
This gives for example
FFV1, https://*org.ffmpeg.trac.*, /wiki/Encode/FFV1, https://*org.ffmpeg.trac.*/wiki/Encode/FFV1
But the link should of course be https://*trac.ffmpeg.org*/wiki/Encode/FFV1
Is there an SQL trick to restore the order of el_to_domain_index to get
the correct URL?
Thanks,
MI