4
\$\begingroup\$
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.

\$\endgroup\$
  • \$\begingroup\$ I'm having nightmares thinking about how to represent this in jOOQ! \$\endgroup\$ – vikarjramun Jul 4 at 15:42
  • 2
    \$\begingroup\$ 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... \$\endgroup\$ – Toby Speight Jul 4 at 15:46
  • \$\begingroup\$ How can we test our own queries on this database? \$\endgroup\$ – dfhwze Jul 4 at 16:49
  • 1
    \$\begingroup\$ @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. \$\endgroup\$ – Retro Jul 4 at 17:06
2
\$\begingroup\$

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
\$\endgroup\$
  • \$\begingroup\$ 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. \$\endgroup\$ – Retro Jul 13 at 21:52
  • \$\begingroup\$ 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. \$\endgroup\$ – Parfait Jul 13 at 23:31
  • \$\begingroup\$ 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. \$\endgroup\$ – Parfait Jul 13 at 23:34
  • 1
    \$\begingroup\$ I have suggested an edit to clarify this in the answer. You are welcome to accept or reject as you see fit. \$\endgroup\$ – Retro Jul 14 at 12:33
  • \$\begingroup\$ 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. \$\endgroup\$ – Retro Jul 14 at 18:07
2
\$\begingroup\$

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;
\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.