Fork of
Hewiki new disambiguations
by IKhitron
This query is marked as a draft
This query has been published
by Virant.
SQL
x
select concat(page, "{{כ}} ([[", disambig, "]])") from
(select distinct disambig, page, ns from
((select concat('# [[', case p1.page_namespace when 0 then '' when 6 then ':קובץ:' when 10 then 'תבנית:'
else 'פורטל:' end,
replace(p1.page_title, "_", " "), ']]') as page,
replace(p2.page_title, "_", " ") as disambig,
p1.page_namespace as ns
from pagelinks join page as p1 join page as p2 join redirect join linktarget
on p1.page_id = pl_from
and lt_title = p2.page_title
and p2.page_id = rd_from
and lt_id = pl_target_id
where lt_namespace = 0
and p1.page_namespace in (0, 6, 10, 100)
and rd_namespace = 0
and rd_title in
(select page_title
from page join categorylinks
on page_namespace = 0
and page_id = cl_from
and cl_to = 'פירושונים')
and not exists
(select * from categorylinks as cl1
where cl1.cl_from = p1.page_id
and cl1.cl_to = 'פירושונים')
and not p2.page_title like "%_(פירושונים)"
and p1.page_is_redirect = 0
and not exists
(select * from templatelinks join linktarget
where tl_from = p1.page_id
and tl_target_id = lt_id
and lt_title = "פירושון")
union
select concat('# [[', case p1.page_namespace when 0 then '' when 6 then ':קובץ:' when 10 then 'תבנית:'
else 'פורטל:' end,
replace(p1.page_title, "_", " "), ']]') as page,
replace(p2.page_title, "_", " ") as disambig,
p1.page_namespace as ns
from page as p1 join pagelinks on p1.page_id = pagelinks.pl_from and p1.page_namespace in (0, 6, 10, 100)
join page as p2 join linktarget
on lt_id = pagelinks.pl_target_id and linktarget.lt_namespace = p2.page_namespace and linktarget.lt_title = p2.page_title
where p1.page_is_redirect = 0
and not p2.page_title like "%_(פירושונים)"
and exists
(select * from categorylinks
where p2.page_id = cl_from
and cl_to = 'פירושונים')
and not exists
(select * from categorylinks
where p1.page_id = cl_from
and cl_to = 'פירושונים')
and not exists
(select * from templatelinks join linktarget
where tl_from = p1.page_id
and tl_target_id = lt_id
and lt_title = "פירושון")
and not (p1.page_namespace = 10
and p1.page_title in ('לטיפול_דחוף', 'לטיפול_דחוף/שינויים_אחרונים'))) as t)
order by disambig, ns, page) tt
By running queries you agree to the Cloud Services Terms of Use and you irrevocably agree to release your SQL under CC0 License.
All SQL code is licensed under CC0 License.