Could you please take a look at User talk:Shirayuki#Redundant anchors and chime in there, if desired? (I'm asking you since you are a translator who has recently edited the page being discussed there.)
User talk:Matěj Suchánek
Jump to navigation
Jump to search
Reply to "Redundant anchors"
My edit to the page is hardly relevant but I will take a look from the administrative point of view.
Hi @Matěj Suchánek:, Could you please help me for two quarry? I need quarry that will give me.
Those users who edited only page namespace 2 & 3.
- User name
- User editcount
- User registration
- User page name
- User page size
- No of page created
Those users who edited only page namespace 2 & 3 and locked/blocked
- User name
- User editcount
- User registration
- User page name
- User page size
- Bane of page created
- Size of page created
First one:
SELECT user.*, COUNT(rev_page) AS created, page_title AS userpage, page_len
FROM actor
JOIN user ON user_id = actor_user
LEFT JOIN revision_userindex ON rev_actor = actor_user AND rev_parent_id = 0
LEFT JOIN page ON page_namespace = 2 AND page_title = REPLACE(user_name, ' ', '_')
WHERE NOT EXISTS (
SELECT 1 FROM revision_userindex AS R1 JOIN page AS P1 ON P1.page_id = R1.rev_page
WHERE R1.rev_actor = actor_id AND P1.page_namespace NOT IN (2, 3)
)
AND user_editcount > 0
GROUP BY actor_id
I tested it in query optimizer but I cannot guarantee you will get your results because it checks all (ever registered) users.
What is Bane of page created?
Thanks!
To find pages that don't have Bengali label at Wikidata but had bnwiki sitelink.
USE wikidatawiki_p;
SELECT CONCAT("Q",ips_item_id), CONCAT("Lbn"), CONCAT('"',ips_site_page,'"')
FROM wb_items_per_site
WHERE ips_site_id='bnwiki' AND NOT EXISTS (
SELECT 1 FROM wb_terms WHERE CONCAT("Q", ips_item_id) = term_full_entity_id AND term_type = "label" AND term_language='bn' LIMIT 1
)
LIMIT 500
;
Now I tried to do with this, but unfortunately the Quarry killed
USE wikidatawiki_p;
SELECT CONCAT("Q",ips_item_id), CONCAT("Lbn"), CONCAT('"',ips_site_page,'"')
from wb_items_per_site
where ips_site_id='bnwiki'
and ips_item_id not in
(
SELECT wbit_item_id
FROM wbt_item_terms
INNER JOIN wbt_term_in_lang tt
ON wbit_term_in_lang_id = tt.wbtl_id
INNER JOIN wbt_text_in_lang dd
ON tt.wbtl_text_in_lang_id = dd.wbxl_id
INNER JOIN wbt_text yy
ON dd.wbxl_text_id = yy.wbx_id
WHERE wbit_item_id = ips_item_id
and dd.wbxl_language = "bn"
and tt.wbtl_type_id = 1
)
LIMIT 500
;
a) Either do:
AND NOT EXISTS ( SELECT wbit_item_id FROM wbt_item_terms ... )
or remove wbit_item_id = ips_item_id
b) Remove
INNER JOIN wbt_text yy ON dd.wbxl_text_id = yy.wbx_id
since you don't use the actual text.
Thanks for response, still same killed. Could you please re-write full code for me.
I suggested two different queries. This is the first one:
USE wikidatawiki_p;
SELECT CONCAT("Q",ips_item_id), "Lbn", CONCAT('"',ips_site_page,'"')
FROM wb_items_per_site
WHERE ips_site_id='bnwiki'
AND NOT EXISTS (
SELECT wbit_item_id FROM wbt_item_terms
INNER JOIN wbt_term_in_lang
ON wbtl_id = wbit_term_in_lang_id
INNER JOIN wbt_text_in_lang
ON wbxl_id = wbtl_text_in_lang_id
WHERE wbit_item_id = ips_item_id
AND wbxl_language = "bn"
AND wbtl_type_id = 1
)
(LIMIT is up to you.) For the second one, https://tools.wmflabs.org/sql-optimizer reports the same plan, so probably not worth trying.
My bot performs this task each week for every wiki. It scans recentchanges
of Wikidata for newly added links and then checks relevant items. So this is an idea for narrowing the query if you don't manage to get any results.
Thanks, max 500 work.
Hi,
I don't know, if you notice new page for translators into czech Project:Translation/cs I added you into list of translators, it's ok?
Ok.
There are no older topics