User talk:Matěj Suchánek

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

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.

1. User name
2. User editcount
3. User registration
4. User page name
5. User page size
6. No of page created

Those users who edited only page namespace 2 & 3 and locked/blocked

1. User name
2. User editcount
3. User registration
4. User page name
5. User page size
6. Bane of page created
7. 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.

Thanks

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.