gistfile1.txt

    $mode = variable_get('comment_default_mode_' . $node->type, COMMENT_MODE_THREADED);
    if ($mode === COMMENT_MODE_FLAT) {
      $field = 'c.cid';
      $order_by = $query->getOrderBy();
      if (isset($order_by[$field])) {
        $query->orderBy($field, 'DESC');
      }
    }
    else {
      // The goal of this alter is to sort comments in the following way:
      // Sort by the thread DESCENDING and by the comment in the thread ASCENDING.
      // Here is an example how it should look like at the end:
      // @code
      //- Comment 3
      //- Comment 2
      //--- Comment 2.1
      //--- Comment 2.2
      //----- Comment 2.2.1
      //- Comment 1
      // @endcode
      // In order to achieve this we leverage the vancode representation of the
      // comments. Therefore we calculate some derived values first:
      // order1: The order of the thread. Therefore we take everything before
      //   the first dot, if available: ow/ -> ow and 0v.00/ -> 0v. This results
      //   in a stable sort of the thread.
      // order2: The order within a specific thread. Here we take everything
      //   after the first dot. To ensure that the thread root comes first, we
      //   use an empty string in this case, otherwise everything after the first
      //   dot. This results in a stable sort of the comments within a thread.
      // +-------+-------+---------------+-----------+--------+---------------------+--------+
      // | cid   | pid   | subject       | thread    | order1 | thread_dot_position | order2 |
      // +-------+-------+---------------+-----------+--------+---------------------+--------+
      // | 74101 |     0 | Comment 3     | 0w/       | 0w     |                   0 |        |
      // | 74100 |     0 | Comment 2     | 0v/       | 0v     |                   0 |        |
      // | 74102 | 74100 | Comment 2.1   | 0v.00/    | 0v     |                   3 | 00     |
      // | 74103 | 74100 | Comment 2.2   | 0v.01/    | 0v     |                   3 | 01     |
      // | 74104 | 74103 | Comment 2.2.1 | 0v.01.00/ | 0v     |                   3 | 01.00  |
      // | 74099 |     0 | Comment 1     | 0u/       | 0u     |                   0 |        |
      // +-------+-------+---------------+-----------+---------+---------------------+-------+
      // With this two properties we can finally sort by order1 DESCENDING and
      // order2 ASCENDING, which results in the expected behaviour.
      // Note: Even we do quite a bit of string manipulation here, its not slow,
      // because we always filter by NID first.
      // Let's restart with the orders.
      $order_by =& $query->getOrderBy();
      $order_by = [];

      // Returns everything on the left side of the first dot, if available.
      $query->addExpression("TRIM(TRAILING '/' FROM SUBSTRING_INDEX(c.thread, '.', 1))", 'order1');
      // Stores the position of the first dot in the thread, as we use it
      // multiple times later
      $query->addExpression("@thread_dot_position := INSTR(c.thread, '.')", 'thread_dot_position');
      // Returns everything on the right side of the first dot.
      $query->addExpression("
CASE
  WHEN @thread_dot_position = 0 THEN ''
  WHEN @thread_dot_position <> 0 THEN TRIM(TRAILING '/' FROM SUBSTR(c.thread, @thread_dot_position + 1))
END
      ", 'order2');
      $query->orderBy('order1', 'DESC');
      $query->orderBy('order2', 'ASC');
    }
  }