Universal-Omega / DynamicPageList3

GNU General Public License v3.0
18 stars 22 forks source link

Fix `usedby` query returns all templates #224

Closed emkarcinos closed 1 year ago

emkarcinos commented 1 year ago

usedby DPL param was returning all templates on a wiki instead of those used by a page.

templatelinks is a many-to-many link that links sources to their link targets. It's used in a query executed by usedby. Because was no condition that would link template targets with their link targets, all templates are returned instead. I'm adding a linking condition that does just that and fixes the issue.

While we're at it, lets rewrite the mechanism of joining results from using WHERE clauses (which are notorious from causing troubles for an SQL engine's optimizer) to use standard JOINs.

The new test data should also cover the surfaced issue.

Query before the fix:

SELECT DISTINCT tplsrc.page_title     AS `tpl_sel_title`,
                tplsrc.page_namespace AS `tpl_sel_ns`,
                `page`.page_namespace AS `page_namespace`,
                `page`.page_id        AS `page_id`,
                `page`.page_title     AS `page_title`
FROM `linktarget` `lt`,
     `page` `tplsrc`,
     `templatelinks` `tpl`,
     `page`
WHERE `page`.page_is_redirect = 0
  AND (`page`.page_namespace = lt.lt_namespace AND `page`.page_title = lt.lt_title AND tplsrc.page_id = tpl.tl_from AND
       (tpl.tl_from = {{page_id}}))
LIMIT 500

Query after the fix (Added AND to link templatelinks target ID with linktarget ID:

SELECT DISTINCT `page`.page_title     AS `tpl_sel_title`,
                `page`.page_namespace AS `tpl_sel_ns`,
                `page`.page_namespace AS `page_namespace`,
                `page`.page_id        AS `page_id`,
                `page`.page_title     AS `page_title`
FROM `page`
         JOIN `linktarget` `lt` ON ((page_title = lt_title) AND (page_namespace = lt_namespace))
         JOIN `templatelinks` `tpl` ON ((lt_id = tl_target_id))
WHERE `page`.page_is_redirect = 0
  AND ((tpl.tl_from = {{page_id}}))
LIMIT 500
Universal-Omega commented 1 year ago

I appreciate the patch. I'll review this tomorrow.