Minion Backend queries by function

query.sql

-- stats()
EXPLAIN SELECT SUM(job.id IS NOT NULL) AS is_active 
FROM minion_workers worker 
LEFT JOIN minion_jobs job ON worker.id = job.worker AND job.state = 'active'
GROUP BY worker.id;

EXPLAIN SELECT state, COUNT(state) AS jobs, SUM(`delayed` > NOW()) AS `delayed`
FROM minion_jobs
GROUP BY state;

-- repair()
EXPLAIN DELETE job
      FROM minion_jobs job
      LEFT JOIN minion_jobs_depends depends ON depends.parent_id = job.id
      LEFT JOIN minion_jobs child ON child.id = depends.child_id AND child.state != 'finished'
      WHERE
        (
          job.expires <= NOW() AND job.state = 'inactive'
        )
        OR (
          job.state = 'finished'
          AND job.`finished` <= DATE_SUB(NOW(), INTERVAL 3600 SECOND)
          AND child.id IS NULL
        );

EXPLAIN select job.id, job.retries from minion_jobs job
left join minion_workers worker on job.worker = worker.id
where state = 'active'
  and queue != 'minion_foreground'
  and worker.id is null;

-- dequeue() -- Add your queue names and task names at the ?s
EXPLAIN SELECT job.id, job.args, job.retries, job.task
FROM minion_jobs job
LEFT JOIN minion_jobs_depends depends ON depends.child_id = job.id
LEFT JOIN minion_jobs parent ON parent.id = depends.parent_id
WHERE job.state = 'inactive'
  AND job.`delayed` <= NOW()
  AND job.queue IN ( ? ) AND job.task IN ( ? )
  AND (job.expires IS NULL OR job.expires > NOW())
GROUP BY job.id
HAVING SUM(
  parent.state IS NOT NULL AND (
    parent.state = 'active'
    OR ( parent.state = 'failed' AND NOT job.lax )
    OR ( parent.state = 'inactive' AND (parent.expires IS NULL OR parent.expires > NOW()))
  )
) = 0;

-- list_jobs()
EXPLAIN SELECT
      id, args, attempts,
      UNIX_TIMESTAMP(created) AS created,
      UNIX_TIMESTAMP(`delayed`) AS `delayed`,
      UNIX_TIMESTAMP(finished) AS finished, lax, priority,
      queue, result, UNIX_TIMESTAMP(retried) AS retried, retries,
      UNIX_TIMESTAMP(started) AS started, state, task,
      GROUP_CONCAT( child_jobs.child_id ORDER BY child_jobs.child_id SEPARATOR ':' ) AS children,
      GROUP_CONCAT( parent_jobs.parent_id ORDER BY parent_jobs.parent_id SEPARATOR ':' ) AS parents,
      worker, UNIX_TIMESTAMP(NOW()) AS time, UNIX_TIMESTAMP(expires) AS expires
    FROM minion_jobs j
    LEFT JOIN minion_jobs_depends child_jobs ON j.id=child_jobs.parent_id
    LEFT JOIN minion_jobs_depends parent_jobs ON j.id=parent_jobs.child_id
    WHERE (state != 'inactive' or expires is null or expires > now())
    GROUP BY j.id, child_jobs.parent_id, parent_jobs.child_id
           , j.args, j.attempts, j.created,
             j.delayed, j.finished, j.lax,
             j.priority, j.queue, j.result,
             j.retried, j.retries, j.started,
             j.state, j.task, j.worker, j.expires
    ORDER BY id DESC;