mother and daughter categories

Fork of pages cathgorized under a mother and daughter categories by Saeidpourbabak

This query is marked as a draft This query has been published by Dalba.

SQL

USE fawiki_p;
# first and second category of an article
SELECT page_title, frst.cl_to, scnd.cl_to
FROM categorylinks AS frst JOIN page ON frst.cl_from = page_id AND page_namespace = 0 AND page_is_redirect = 0
JOIN categorylinks AS scnd ON scnd.cl_from = page_id
AND frst.cl_to <> scnd.cl_to

# none is hidden
WHERE NOT EXISTS (
  SELECT NULL
  FROM page JOIN page_props ON page_id = pp_page
  WHERE page_namespace = 14
  AND page_is_redirect = 0
  AND (
    page_title = frst.cl_to
    OR page_title = scnd.cl_to
  )
  AND pp_propname = 'hiddencat'
)

# scnd is mother of frst
AND scnd.cl_to IN (
  SELECT thrd.cl_to
    FROM categorylinks AS thrd JOIN page ON cl_from = page_id
    WHERE page_title = frst.cl_to
    AND page_namespace = 14
    AND page_is_redirect = 0
  )

AND page_title = 'خیام'

LIMIT 100;

SQL

Checking query status...