joomla-cms Optimize scheduler running plugin

Summary of Changes

PlgSystemSchedulerunner::injectLazyJS() checks the presence of due tasks via weird loading of items list with order by title DESC using the admin model:

SELECT a.id, a.asset_id, a.title, a.type, a.execution_rules, a.state, a.last_exit_code, a.locked, a.last_execution, a.next_execution, a.times_executed, a.times_failed, a.ordering, a.note
FROM `jos_scheduler_tasks` AS `a`
WHERE `a`.`state` = :state AND `a`.`next_execution` <= :now
ORDER BY `a`.`title` desc

Assuming that we are not loading the data but only counting the tasks which should be executed, we should not retrieve the full list of items, but only count them.

We have a second weird query to check if there are any locked tasks, the same invalid behavior occurs again:

SELECT a.id, a.asset_id, a.title, a.type, a.execution_rules, a.state, a.last_exit_code, a.locked, a.last_execution, a.next_execution, a.times_executed, a.times_failed, a.ordering, a.note
FROM `#__scheduler_tasks` AS `a`
WHERE `a`.`state` = :state AND `a`.`locked` IS NOT NULL
ORDER BY `a`.`title` desc

These two queries can be optimized into single efficient query which loads the number of due tasks + the number of locked tasks:

SELECT SUM(CASE WHEN `a`.`next_execution` <= :now THEN 1 ELSE 0 END) AS due_count, SUM(CASE WHEN `a`.`locked` IS NULL THEN 0 ELSE 1 END) AS locked_count
FROM `#__scheduler_tasks` AS `a`
WHERE `a`.`state` = :state

Using backend list model is not efficient, better compose the query in the plugin.

The result of single query is checked and the plugin is not adding JS if we don't have due tasks OR we have locked tasks.

Testing Instructions

Test scheduled tasks

Actual result BEFORE applying this Pull Request

See two queries for #__scheduler_tasks table.

Expected result AFTER applying this Pull Request

See one query for #__scheduler_tasks table. com_scheduler works as before.

Documentation Changes Required

No.

Feb 23 '22 13:02 Denitz

It was a deliberate decision to avoid any queries that bypass the model. That said I really like this, but could we instead add this as a method in the Scheduler model which could be then accessed through the Scheduler API class? That way we could avoid any localized logic in the plugin and also increase the useful API surface of the Scheduler which could be useful to other extensions. Thank you for working on this!