Performance optimization for quiz_question_relationship hierarchy ordering in Quiz::buildLayout()

Problem/Motivation
In the current implementation of the Quiz::buildLayout() method (within quiz/src/Entity/Quiz.php), there's a SQL query used to fetch quiz questions and sort them by parent/child hierarchy.

This query includes a LEFT JOIN with an OR condition:

LEFT JOIN {quiz_question_relationship} qqr2
  ON (qqr.qqr_pid = qqr2.qqr_id OR (qqr.qqr_pid IS NULL AND qqr.qqr_id = qqr2.qqr_id))

This causes performance issues in large datasets (e.g., 60,000+ records), as verified using EXPLAIN which reports

EXPLAIN SELECT qqr.question_id as qqid, qqr.question_vid as vid, qq.type, qqr.qqr_id, qqr.qqr_pid, qq.title FROM quiz_question_relationship qqr JOIN quiz_question qq ON qqr.question_id = qq.qqid LEFT JOIN quiz_question_relationship qqr2 ON (qqr.qqr_pid = qqr2.qqr_id OR (qqr.qqr_pid IS NULL AND qqr.qqr_id = qqr2.qqr_id)) WHERE qqr.quiz_vid = '1822' AND qqr.question_status = '1' ORDER BY qqr2.weight, qqr.weight;

Proposed resolution
Update the query in Quiz::buildLayout() to use a simpler LEFT JOIN and COALESCE() function to preserve logic without OR.

🔧 Optimized version:

$query = \Drupal::database()->query('
  SELECT qqr.question_id as qqid, qqr.question_vid as vid, qq.type, qqr.qqr_id, qqr.qqr_pid, qq.title,
         COALESCE(qqr2.weight, qqr.weight) AS parent_weight
  FROM {quiz_question_relationship} qqr
  JOIN {quiz_question} qq ON qqr.question_id = qq.qqid
  LEFT JOIN {quiz_question_relationship} qqr2 ON qqr.qqr_pid = qqr2.qqr_id
  WHERE qqr.quiz_vid = :quiz_vid
    AND qqr.question_status = :question_status
  ORDER BY parent_weight, qqr.weight
', [
  ':quiz_vid' => $this->getRevisionId(),
  ':question_status' => QuizQuestion::QUESTION_ALWAYS,
]);

EXPLAIN SELECT qqr.question_id AS qqid, qqr.question_vid AS vid, qq.type, qqr.qqr_id, qqr.qqr_pid, qq.title,
       COALESCE(parent.weight, qqr.weight) AS parent_weight
FROM quiz_question_relationship qqr
JOIN quiz_question qq ON qqr.question_id = qq.qqid
LEFT JOIN quiz_question_relationship parent ON qqr.qqr_pid = parent.qqr_id
WHERE qqr.quiz_vid = '1822' AND qqr.question_status = '1'
ORDER BY parent_weight, qqr.weight;