1

First, Wikipedia's database scheme diagram is available here. I have used one of their dumps locally to work with it.

Now, I need a single MySQL query to check both page and redirect tables with a given input 'Dog_breeder' or 'Dog_breeding' for example. I want to get the list of both types of pages linked to this input.

For example, Dog_breeding is a main topic and the below pages will redirect to it:

  • Dog_Breeder
  • Dog_breeder
  • Dog_breeders
  • Stud_dog

In the database, this is stored in this fashion:

|---------------------------|       |---------------------------|
| table: page               |       | table: redirect           |
|---------------------------|       |---------------------------|
| page_id   | page_title    |       | rd_from   | rd_title      |
|---------------------------|       |---------------------------|
| 476072    | Dog_breeding  |       | 796236    | Dog_breeding  |
| 796236    | Dog_breeder   |       | 16274701  | Dog_breeding  |
| 16274701  | Dog_breeders  |       | 37353772  | Dog_breeding  |
| 37353772  | Stud_dog      |       | 40738352  | Dog_breeding  |
| 40738352  | Dog_Breeder   |       |---------------------------|
| 11606599  | Dog_breeders  |       
| 22534134  | Dog_breeders  |       
|---------------------------|       

So all pages are in the page table and then redirections are stored in the redirect table (rd_from is the page that will be redirected to the main rd_title page)

I have the query to retrieve all redirect entries of a main topic:

SELECT 
    page_id, 
    page_title, 
    rd_from,
    rd_title
FROM 
    redirect 
    JOIN page ON page_id = rd_from
WHERE rd_title = 'Dog_breeding'

But two problems:

  1. it is not giving me the main page itself ('Dog_breeding')
  2. if I put WHERE rd_title = 'Dog_breeder' it finds nothing...

I would like to get something like the below regardless of the input being Dog_breeding or Dog_breeder:

page_id     | page_title    | rd_from   | rd_title  
-------------------------------------------------------
476072      | Dog_breeding  |           | 
796236      | Dog_breeder   | 796236    | Dog_breeding
16274701    | Dog_breeders  | 16274701  | Dog_breeding
37353772    | Stud_dog      | 37353772  | Dog_breeding
40738352    | Dog_Breeder   | 40738352  | Dog_breeding

I guess I could do it with PHP and several back and forth with the Database (check if it's a main topic, if not grab the main topic, then query like I did above) but I feel like there is a one-query solution?

  • it doesn't actually matter if I SELECT all of them or just unique terms, if that helps building the query? I can then filter the final result by removing duplicates. – Lazhar Jan 1 '17 at 12:38
  • Indeed, but that's a little irrelevant for the second problem i am facing, which is retrieving the same result set whether I am querying the main page (not redirected) or a redirect (redirected) – Lazhar Jan 1 '17 at 12:46
  • @McNets it's a difficult one but i feel like it's doable.. it's easy to write two separate queries... 1) one when we know the input is a main page 2) one when we know the input is a redirect... but so difficult to mix and marry them so they can retrieve the same set whether the input is one or the other :/ – Lazhar Jan 1 '17 at 12:51
  • Cross-posted: dba.stackexchange.com/q/159686/10294, stackoverflow.com/q/41414447/781723. Please do not post the same question on multiple sites. Each community should have an honest shot at answering without anybody's time being wasted. – D.W. Jan 2 '17 at 3:08
1

Initially it seems pretty easy to solve the question, by adding a new criteria to the WHERE clause.

SELECT 
    page.page_id, 
    page.page_title, 
    redirect.rd_from,
    redirect.rd_title
FROM 
    redirect 
    JOIN page ON page.page_id = redirect.rd_from
WHERE 
    redirect.rd_title = 'Dog_breeding' or page.page_title = 'Dog_breeding'

But as Lazhar has pointed out, this only works if the requested text belongs to the main wiki page, not for the redirected pages.

After taking a look at table definitions:

MediaWiki - Manual:page table

page_is_redirect A value of 1 here indicates the article is a redirect; it is 0 in all other cases

MediaWiki - Manual:redirect table

Contains for each page that is currently a redirect (i.e., not for old revisions which are redirects) the id of the source page and the information about target. The target page may or may not exist.

I've decided to use this field to solve the problem.

First we need to know the title of the main page, the page that is not a redirection. In this question: Dog_breeding

select page.page_title as title from page where page.page_title = 'Dog_breeders' and page_is_redirect = 0
union
select redirect.rd_title as title 
from redirect 
where redirect.rd_from in (select page.page_id from page where page.page_title = 'Dog_breeders' and page_is_redirect = 1) 

Then using the first approach:

SELECT 
    page.page_id, 
    page.page_title, 
    redirect.rd_from,
    redirect.rd_title
FROM 
    page 
    left JOIN redirect ON page.page_id = redirect.rd_from
    join (select page.page_title as title from page where page.page_title = 'Dog_breeders' and page_is_redirect = 0
          union
          select redirect.rd_title as title 
          from redirect 
          where redirect.rd_from in (select page.page_id from page where page.page_title = 'Dog_breeders' and page_is_redirect = 1) 
          ) cteTitle 
WHERE 
    redirect.rd_title = cteTitle.title or page.page_title = cteTitle.title;

This is the result:

+----------+--------------+----------+--------------+
|  page_id |  page_title  |  rd_from |   rd_title   |
+----------+--------------+----------+--------------+
|  796236  |  Dog_breeder |  796236  | Dog_breeding |
+----------+--------------+----------+--------------+
| 16274701 | Dog_breeders | 16274701 | Dog_breeding |
+----------+--------------+----------+--------------+
| 37353772 |   Stud_dog   | 37353772 | Dog_breeding |
+----------+--------------+----------+--------------+
| 40738352 |  Dog_Breeder | 40738352 | Dog_breeding |
+----------+--------------+----------+--------------+
|  476072  | Dog_breeding |   NULL   |     NULL     |
+----------+--------------+----------+--------------+

Can check it here: http://rextester.com/OQMV97513

NOTE: There is another solution using pagelinks table in SO

  • But that's good if the input is the actual 'main page' (that other pages redirect to). How to get that same result regardless of the input being the main page or a redirect page (such as 'Dog_breeder' or 'Stud_dog')? – Lazhar Jan 1 '17 at 11:49
  • No, a redirected page (listed in redirect) only goes to one single main page. – Lazhar Jan 1 '17 at 12:08
  • It is, two of them are ignored and don't show on wikipedia but they still are in their MySQL dump so yep, I put them in there just to be as close to reality as possible. – Lazhar Jan 1 '17 at 12:11
  • Definitely works and gives the expected result. Now, the querying is rather slow on the actual Wiki's DB (5GB rows and 5GB indexes) but that's another story! Thanks for finally finding a way, because that's been ruining my brain today.... – Lazhar Jan 1 '17 at 17:42
  • I've found another question about slow queries, is there an index for 'title'; – McNets Jan 1 '17 at 17:44
0

I've adopted a different approach since the unique mysql query did not seem feasible.. I did my PHP script to first try if it's a main topic, and if not, check if it's a redirected one.

And then, I fetch accordingly to output a normalized json.

<?php

if( isset($_GET['input']) && !empty(trim($_GET['input'])) ) {

    $time_start = microtime(true);

    $input = trim($_GET['input']);
    $mysqli = new mysqli("localhost", "root", "root", "topicmesh");

    if ($mysqli->connect_errno) {
        printf("Connect failed: %s\n", $mysqli->connect_error);
        exit();
    }

    $topics = array(
        'input'             => $input,
        'is_main'           => false,
        'exec_time'         => 0,
        'main_topic'        => array(),
        'surface_forms'     => array()
    );


    // 
    // Check if the input is a main topic
    // 

    $query = "

        SELECT 
            page_title,
            rd_title as TargetTopic
        FROM
            redirect
            JOIN page ON page_id=rd_from
        WHERE
            rd_title = '$input'

        UNION

        SELECT
            page_title,
            '-' as TargetTopic
        FROM
            page
        WHERE
            page_title = '$input'

        ORDER BY 
            `TargetTopic` ASC,
            page_title ASC

    ";
    $result = $mysqli->query($query);

    if ( $result->num_rows <= 1 ) {

        // 
        // Check if the input is a redirected topic
        // 

        $query = "
            SELECT
                page.page_title,
                ( SELECT rd_title FROM redirect WHERE rd_from = ( SELECT page_id FROM page WHERE page_title = '$input' ) ) as TargetTopic
            FROM
                page
            WHERE
                page.page_id IN ( SELECT redirect.rd_from FROM redirect WHERE redirect.rd_title = ( SELECT rd_title FROM redirect WHERE rd_from = ( SELECT page_id FROM page WHERE page_title = '$input' ) ) )

            UNION

            SELECT
                P2.page_title,
                '-' as TargetTopic
            FROM
                page P2
            WHERE
                P2.page_title = ( SELECT rd_title FROM redirect WHERE rd_from = ( SELECT page_id FROM page WHERE page_title = '$input' ) )  
            ORDER BY 
                `TargetTopic` ASC,
                page_title ASC
        ";
        $result = $mysqli->query($query);

    }

    if ( $result ) {

        $i = 0;
        $sf_count = 0;

        foreach ($result as $item) {

            if( $i == 0 ) {
                $topics['main_topic'] = array(
                    'label'     => $item['page_title'],
                    'sf_count'  => 0
                );
            } else {
                $topics['surface_forms'][] = array(
                    'label' => $item['page_title']
                );
                $sf_count++;
            }

            $i++;
        }

        $result->free();

    }

    $topics['main_topic']['sf_count'] = count($topics['surface_forms']);

    if( !isset( $topics['main_topic']['label'] ) ) {
        $topics['is_empty'] = true;
    } else {
        if( $input == $topics['main_topic']['label'] ) {
            $topics['is_main'] = true;
        }
    }

    $time_end = microtime(true);
    $time = $time_end - $time_start;
    $topics['exec_time'] = $time;

    echo '<pre>'.json_encode($topics, JSON_PRETTY_PRINT).'</pre>';

    $mysqli->close();

}

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.