Finding Wikipedia articles with specific types of user page links

Question

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
      FROM templatelinks
      WHERE
        tl_from = pl_from
        AND tl_title = 'Under_construction'
    ) THEN '(C)'
    ELSE ''
  END AS C,
  CASE
    WHEN EXISTS (
      SELECT 1
      FROM categorylinks
      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
      FROM templatelinks
      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
      FROM templatelinks
      WHERE
        tl_from = pl_from
        AND tl_title = 'Copyvio-revdel'
    ) THEN '(RD1)'
    ELSE ''
  END AS RD1,
  CASE
    WHEN EXISTS (
      SELECT 1
      FROM templatelinks
      WHERE
        tl_from = pl_from
        AND tl_title = 'Wikipedia_person_user_link'
    ) THEN '(WP)'
    ELSE ''
  END AS WP,
  CASE
    WHEN EXISTS (
      SELECT 1
      FROM categorylinks
      WHERE
        cl_from = pl_from
        AND cl_to = 'Candidates_for_speedy_deletion'
    ) THEN '(CSD)'
    ELSE ''
  END AS CSD
FROM pagelinks
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
      FROM templatelinks
      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.

Answer 1

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
            WHEN t.t_title = 'Wikipedia_person_user_link'
            THEN '(WP)'
            ELSE ''
          END AS WP,
          CASE
            WHEN c.cl_to = 'Candidates_for_speedy_deletion'
            THEN '(CSD)'
            ELSE ''
          END AS CSD
    FROM pagelinks pl
    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

Answer 2

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
      FROM templatelinks
      WHERE
        tl_from = pl_from
        AND tl_title = 'template_title'
    ) THEN '(col_name)'
    ELSE ''
  END AS col_name

select (ifnull((
    select '(col_name)' 
    from templatelinkswhere 
    where tl_from = pl_from 
    and tl_title = 'template_title')
    , '')
) as col_name;