Finding Wikipedia articles with specific types of user page links

SELECT pl_from, NS, page_title, L_NS, L_titles, num_L, SB, IU, WP
FROM (
SELECT
pl_from,
-- pl_from_namespace does not appear to be consistently reliable;
-- it might be better to select from the page table and join the pagelinks table to it.
CASE
-- This fails on pages missing from the page table (presumably because they were deleted).
WHEN pl_from_namespace != page_namespace THEN CONCAT(pl_from_namespace, ' vs. ', page_namespace)
ELSE pl_from_namespace
END AS NS,
page_title,
pl_namespace AS L_NS,
GROUP_CONCAT(pl_title SEPARATOR ' ') AS L_titles,
COUNT(pl_title) AS num_L,
CASE
WHEN MAX(CASE WHEN pl_title LIKE '%/sandbox' THEN 1 END) = 1 THEN '(SB)'
ELSE ''
END AS SB,
CASE
WHEN EXISTS (
SELECT 1
WHERE
tl_from = pl_from
AND tl_title = 'Under_construction'
) THEN '(C)'
ELSE ''
END AS C,
CASE
WHEN EXISTS (
SELECT 1
WHERE
cl_from = pl_from
AND cl_to = 'Pages_using_Under_construction_with_the_placedby_parameter'
) THEN '(PB)'
ELSE ''
END AS C_PB,
CASE
WHEN EXISTS (
SELECT 1
WHERE
tl_from = pl_from
AND (tl_title = 'In use' OR tl_title = 'GOCEinuse')
) THEN '(IU)'
ELSE ''
END AS IU,
CASE
WHEN EXISTS (
SELECT 1
WHERE
tl_from = pl_from
AND tl_title = 'Copyvio-revdel'
) THEN '(RD1)'
ELSE ''
END AS RD1,
CASE
WHEN EXISTS (
SELECT 1
WHERE
tl_from = pl_from
) THEN '(WP)'
ELSE ''
END AS WP,
CASE
WHEN EXISTS (
SELECT 1
WHERE
cl_from = pl_from
AND cl_to = 'Candidates_for_speedy_deletion'
) THEN '(CSD)'
ELSE ''
END AS CSD
LEFT JOIN page ON page_id = pl_from
WHERE
pl_from_namespace = 0
AND pl_namespace = 2
-- In the future: AND pl_namespace != 0
GROUP BY pl_from
ORDER BY SB, page_title
) AS t1
WHERE
(
(C = '' AND RD1 = '' AND WP = '' AND CSD = '')
OR num_L != 1
)
AND (C_PB = '' OR num_L != 2)


Currently this query is run on an online database replicate of Wikipedia, so you can see this query's result.

What is this supposed to do? How does it work?

Relevant background: MediaWiki wikis separates pages into namespaces that are intended to store different types of content. On Wikipedia, the article namespace (which contains all of the actual encyclopedia) is the main namespace. Namespaces are somewhat analogous to how Stack Exchange sites separate content into the main questions site and the Meta domain for internal site discussion (however, Wikipedia sorts many things into namespaces that SE sites don't).

This query searches for internal links from articles to the user namespace. Getting all these links is easy; the complexity arises from filtering out some of the results under specific conditions.

Before going further, there's one essential piece of background about the MediaWiki database schema: the columns of each table are specifically named to be distinct, so each column starts with a prefix specific to its originating table. This query uses 4 tables: page with prefix page_, pagelinks with prefix pl_, templatelinks with prefix tl_, and categorylinks with prefix cl_.

The basic goals of the query are as follows:

• Take all link on articles (i.e. pagelinks rows where pl_from_namespace = 0) that link to user namespace (i.e. where pl_namespace = 2) from the pagelinks table
• GROUP BY pl_from to allow counting of links per page through COUNT(pl_title) AS num_L, and to generally organize rows as page-specific.
• This also uses the aggregate function GROUP_CONCAT(pl_title SEPARATOR ' ') AS L_titles to list each link in the final output output.
• Filter out page rows that:
• have any of the following templates: 'Under_construction', 'Copyvio-revdel', 'Wikipedia_person_user_link' or has the category 'Candidates_for_speedy_deletion'
• (A page "has a template" if there's a row in templatelinks where tl_from = pl_from AND tl_title = 'Template_title'. A page "has a category" if there's a row in categorylinks where cl_from = pl_from AND cl_to = 'Category_title')
• AND have one link (numL = 1)
• Additionally filter out page rows that have the category 'Pages_using_Under_construction_with_the_placedby_parameter' AND have two links (numL = 2)
• Take note of rows that have a page_title ending with /sandbox or have the template 'In_use' or 'GOCEinuse' by using what amounts to Boolean columns.

These documentation links may also be useful if one wants to understand the Mediawiki database better, but should not be required to answer the question: Database layout manual, page table, pagelinks table, templatelinks table, categorylinks table

Improvements I'm looking for

My query already runs in less than a second, so I'm not too concerned about efficiency (though I welcome any suggestions).

What I'm mainly looking for is general SQL advice. I strongly suspect there are much much better ways to handle repeated structures like:

  CASE
WHEN EXISTS (
SELECT 1
WHERE
tl_from = pl_from
AND tl_title = 'template_title'
) THEN '(col_name)'
ELSE ''
END AS col_name


If you need any clarification or have any questions, feel free to comment and I will reply.

• I'm having nightmares thinking about how to represent this in jOOQ! Jul 4, 2019 at 15:42
• It's a pleasant change to see a sql question with a decent problem statement, tables description and the rest. Welcome to Code Review, and please help sustain this high standard! Sorry I'm not qualified enough in SQL to review it myself... Jul 4, 2019 at 15:46
• How can we test our own queries on this database? Jul 4, 2019 at 16:49
• @dfhwze To access Quarry, the website where I ran the query, you'll have to create an account on any Wikimedia wiki (accounts are global, so an account on the English Wikipedia works) and then connect to Quarry through OAuth (it's straightforward). Alternatively, a less convenient option is to download a Wikipedia database dump. There are other APIs to connect to Wikipedia database replicates, but I'm not familiar with them; generally, they will also require a Wikimedia wiki account. Jul 4, 2019 at 17:06

Consider the following tips and best practices:

1. TABLE QUALIFIERS: First and foremost, always qualify all fields in all clauses (SELECT, WHERE, JOIN, etc.) with table names or table aliases using period denotation. Doing so facilitates readability and maintainability.

2. PREFIXED FIELD NAMES: Related to above, avoid prefixing field names (pl_from, tl_title, cl_to). Instead, use table aliases to period qualify identifiers in query to avoid collision or confusion. Of course if this is Wikimedia's setup, there's nothing you can do.

3. CASE SUBQUERIES: Avoid subqueries in CASE statements which requires row by row logic calculation. Instead, use multiple LEFT JOIN on templatelinks and categorylinks tables and then run the needed CASE logic where NOT EXISTS render as NULL.

4. GROUP BY: Unfortunately, at a disservice to newcomers in SQL due to MySQL's ONLY FULL GROUP BY mode turned off, your aggregate inner query is not ANSI compliant. Always include all non-aggregated columns in GROUP BY for consistent, valid results.

Your query would fail in practically all other RDBMS's (Oracle, Postgres, etc.) as your GROUP BY query is incomplete and does not adhere to ANSI rules since page_title, pl_namespace, and now the new LEFT JOIN fields are not included. In SQL where at least one aggregate is used such as COUNT, all grouped columns must be included in GROUP BY clause but can be optionally omitted in SELECT (not other way around). NOTE: your results may change with such code refactoring. The Wikimedia interface may not allow setting/mode adjustments.

5. AGGREGATION: Related to above, you may need to handle all unit level calculations including CASE statements in the inner query and move aggregation to top level SELECT. If you need to include other unit level fields in final resultset but not in aggregation, run a JOIN on the aggregated subquery or via a CTE.

Below is an adjustment to your SQL query with unit level calculations handled in derived table subquery and all aggregations moved to top level. Previous outer WHERE now becomes HAVING since aggregates are involved. Depending on your needs and results, additional adjustments may be needed. But again, be sure to run with complete GROUP BY to include all non-aggregated columns. As mentioned, you will not be warned by the Wikimedia engine.

SELECT sub.pl_from,
sub.page_title,
sub.L_NS,
MAX(sub.NS) AS NS,
GROUP_CONCAT(sub.pl_title SEPARATOR ' ') AS L_titles,
COUNT(sub.pl_title) AS num_L,
MAX(sub.SB) AS SB,
MAX(sub.IU) AS IU,
MAX(sub.WP) AS WP
FROM
(SELECT pl.pl_from,
CASE
WHEN pl.pl_from_namespace != p.page_namespace
THEN CONCAT(pl.pl_from_namespace, ' vs. ', p.page_namespace)
ELSE pl.pl_from_namespace
END AS NS,
p.page_title,
pl.pl_namespace AS L_NS,
pl.pl_title,
CASE
WHEN MAX(CASE WHEN pl.pl_title LIKE '%/sandbox' THEN 1 END) = 1 THEN '(SB)'
ELSE ''
END AS SB,
CASE
WHEN t.tl_title = 'Under_construction'
THEN '(C)'
ELSE ''
END AS C,
CASE
WHEN c.cl_to = 'Pages_using_Under_construction_with_the_placedby_parameter'
THEN '(PB)'
ELSE ''
END AS C_PB,
CASE
WHEN (t.tl_title = 'In use' OR t.tl_title = 'GOCEinuse')
THEN '(IU)'
ELSE ''
END AS IU,
CASE
WHEN t.tl_title = 'Copyvio-revdel'
THEN '(RD1)'
ELSE ''
END AS RD1,
CASE
THEN '(WP)'
ELSE ''
END AS WP,
CASE
WHEN c.cl_to = 'Candidates_for_speedy_deletion'
THEN '(CSD)'
ELSE ''
END AS CSD
LEFT JOIN page p ON p.page_id = pl.pl_from
LEFT JOIN templatelinks t ON t.tl_from = pl.pl_from
LEFT JOIN categorylinks c ON c.cl_from = pl.pl_from

WHERE pl.pl_from_namespace = 0
AND pl.pl_namespace = 2
) AS sub

GROUP BY sub.pl_from,
sub.page_title,
sub.L_NS
HAVING
(
(MAX(sub.C) = '' AND MAX(sub.RD1) = '' AND MAX(sub.WP) = '' AND MAX(sub.CSD) = '')
OR COUNT(sub.pl_title) != 1
)
AND (MAX(sub.C_PB) = '' OR COUNT(sub.pl_title) != 2)

ORDER BY MAX(sub.SB),
sub.page_title

• Always include non-aggregated columns in GROUP BY for consistent, valid results. I'm a bit confused. Are you talking about the other aggregated columns in the SELECT statement? Because pl.pl_from isn't itself aggregated. Jul 13, 2019 at 21:52
• Any non-aggregated columns should be placed in the GROUP BY clause. Right now you only include pl_from but not page_title, pl_namespace, etc. Basically, anything that is NOT called in COUNT, SUM, MIN, MAX, AVG, GROUP_CONCAT, and other aggregates should be in GROUP BY. Jul 13, 2019 at 23:31
• And intuitively, the correct use of aggregate query makes sense semantically because your count and group_concat is grouped by the listed groupings. Right now, it is unclear what the count is since it is adjacent to non-aggregated unit level fields. Other RDBMS would raise the needed error such as SQL Server: Column 'XXXXX' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. Or Postgres: ERROR: column "XXXXC" must appear in the GROUP BY clause or be used in an aggregate function. Jul 13, 2019 at 23:34
• I have suggested an edit to clarify this in the answer. You are welcome to accept or reject as you see fit. Jul 14, 2019 at 12:33
• Currently, I like your answer the best, so I will accept it. However, I still don't fully understand the issue with GROUP BY and aggregate functions (I understand it in the abstract, but I haven't quite gotten an intuitive grasp of why it's necessary or how I would fix it in practice), so I would appreciate you finishing the code so there's a working example of how to apply it to this situation. That is not to say that your answer is necessarily lacking on the explanation side, and I think I will be able to understand it on my own if I spend a bit more time pondering it. Jul 14, 2019 at 18:07

I strongly suspect there are much much better ways to handle repeated structures like:

In mysql, you can use ifnull to shorten this:

 CASE
WHEN EXISTS (
SELECT 1
WHERE
tl_from = pl_from
AND tl_title = 'template_title'
) THEN '(col_name)'
ELSE ''
END AS col_name

select (ifnull((
select '(col_name)'