Talk:Quarry

About this board

Previous discussion was archived at Talk:Quarry/Archive 1 on 2015-04-17. Discussion area for discussion about Quarry itself and help about individual queries.

pagelinks doesn't work on commonswiki_p

4
Achim55 (talkcontribs)

Even 'DESCRIBE pagelinks' throws 'Error: View 'commonswiki_p.pagelinks' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them'. Other wiki's DBs are fine.

Achim55 (talkcontribs)

What a miracle, now it works again...

BDavis (WMF) (talkcontribs)

Less of a miracle than things working as intended. phab:T352010#9551548 was the bug report in a location that folks would be looking for it, 4 minutes later phab:T352010#9551559 was the realization of what was needed to correct the issue, and finally phab:T352010#9551564 6 minutes after the initial report was the DBA asking for verification that the fix worked.

Matěj Suchánek (talkcontribs)
Reply to "pagelinks doesn't work on commonswiki_p"

Need Help, for Missing Infobox items

4
IJohnKennady (talkcontribs)

Hi Supports, How to find the missing or empty Infobox parameters, for e-g, Infobox Indian constituency this template have multiple parameters, find this "| constituency_no" and "| constituency_no = " is blank/empty. - IJohnKennady (talk) 17:22, 20 January 2024 (UTC)

Matěj Suchánek (talkcontribs)

Infobox parameters are not indexed by the database.

IJohnKennady (talkcontribs)

Is there any wiki tools available to find??? -

Matěj Suchánek (talkcontribs)

CirrusSearch, a.k.a. the native search functionality, comes to my mind. In particular, its hastemplate: and insource: features.

Reply to "Need Help, for Missing Infobox items"

how to get articles with images of a category

2
Info-farmer (talkcontribs)

I want to get articles with images of a the English Wikipedia category. Please help.

https://en.wikipedia.org/wiki/Category:Critically_endangered_plants

i tried by the code

select p.page_title from page p,page p1 where p.page_id in (select cl_from from categorylinks where cl_to = "Critically endangered plants") and p.page_namespace=0 and p1.page_namespace=1 and p1.page_title=p.page_title;
Matěj Suchánek (talkcontribs)

Would joining the page_props table satisfy your needs?

SELECT ..., pp_value
[...]
LEFT JOIN page_props ON pp_page = p.page_id AND pp_propname = 'page_image_free'
Reply to "how to get articles with images of a category"

Unknown column 'el_to' in 'field list'

3
Ameisenigel (talkcontribs)

Has anyone an idea what I can do to resolve the error "Unknown column 'el_to' in 'field list'" for the following query:

use commonswiki_p;

SELECT concat("M",page_id) AS MID, "P6305",

concat('"',REPLACE(el_to,'https://ticket.wikimedia.org/otrs/index.pl?Action=AgentTicketZoom&TicketNumber=',''),'"') AS OTRS,

    count(el_to) as num

FROM page

JOIN categorylinks c1 ON page_id=c1.cl_from AND c1.cl_to='Files_with_PermissionTicket_template_but_without_P6305_SDC_statement'

JOIN templatelinks ON page_id=tl_from AND tl_target_id = 242

JOIN externallinks ON page_id=el_from AND el_to LIKE 'https://ticket.wikimedia.org/otrs/index.pl?Action=AgentTicketZoom&TicketNumber=%'

group by MID

having num=1
Matěj Suchánek (talkcontribs)

el_to has been split into el_to_domain_index and el_to_path. Note that el_to_domain_index is an inverted index. Your query should now look like WHERE el_to_domain_index = 'https://org.wikimedia.ticket.' AND el_to_path LIKE '/otrs/index.pl?Action=AgentTicketZoom&TicketNumber=%'.

Ameisenigel (talkcontribs)

Thank you! That worked.

Miraburu (talkcontribs)
Matěj Suchánek (talkcontribs)

I think you should add AND rev_parent_id = 0. Then, you will have 1:1 relationship and won't need GROUP BY.

Miraburu (talkcontribs)

Thanks!

ZI Jony (talkcontribs)

I'm trying to run this query, I also tried this and this to get results, but both executed with no data! According to the external links search there is some data to be replaced.

Matěj Suchánek (talkcontribs)

el_to_domain_index is an inverted index to allow search within parent domain. el_to_domain_index = 'http://org.banglapedia.en.' will select all links to http://en.banglapedia.org.

ZI Jony (talkcontribs)
Matěj Suchánek (talkcontribs)

It seems that WHERE ... OR ... AND page_namespace = 0 is interpreted as WHERE ... OR (... AND page_namespace = 0). You need to add parentheses like WHERE (... OR ...) AND page_namespace = 0.

ZI Jony (talkcontribs)

Thanks, it’s working well.

Can I add putout to page?

1
維基小霸王 (talkcontribs)
Reply to "Can I add putout to page?"

Is there a way to query multiple databases?

2
ChaoticBeast (talkcontribs)

I am trying to write a sql query to know the number of articles in each language's Wikipedia database. I was wondering if there was a way to query multiple databases in a single shot query?

Matěj Suchánek (talkcontribs)

Probably not using Quarry, but you could request access to the database replicas via Toolforge and do that using shell.

Reply to "Is there a way to query multiple databases?"

Find list of new pages created in main space in a specified month? on te wiki

3
హరుడు (talkcontribs)

Find list of new pages created in main space in a specified month? example: november, 2019

Matěj Suchánek (talkcontribs)
SELECT page_title, rev_timestamp, actor_name
FROM revision
JOIN page ON page_id = rev_page
JOIN actor_revision ON actor_id = rev_actor
WHERE rev_timestamp LIKE '201911%'
AND rev_parent_id = 0
AND page_namespace = 0
AND page_is_redirect = 0
Jonathan3 (talkcontribs)
Reply to "Find list of new pages created in main space in a specified month? on te wiki"
Gonnym (talkcontribs)

I'm trying to create a query to find all redirects that use the template "R_from_television_episode" and do not have a redirect target that is "ep%". I've written this code but it doesn't work. Any help would be appreciated.

SELECT
  page_title,
  rd_title,
  rd_fragment
FROM
  page
  JOIN redirect ON page.page_id = redirect.rd_from
  JOIN templatelinks ON templatelinks.tl_from = page.page_id
  JOIN linktarget ON linktarget.lt_id = templatelinks.tl_target_id
WHERE
  rd_fragment NOT LIKE 'ep%'
  AND linktarget.lt_title = "R_from_television_episode"
ORDER BY
  page_title
Matěj Suchánek (talkcontribs)

I think you need to specify AND linktarget.lt_namespace = 10, too. If it's performance issue, you can try adding page_is_redirect = 1 and removing ORDER BY.

Gonnym (talkcontribs)

Thanks, that helped!