Posts with the most one-level children comments (Drupal 8-10)

comment_child_count.sql

Without Subselect:

SELECT count(child.cid), parent.cid, user.uid, user.name, node_field.nid, node_field.title
FROM comment_field_data as parent
LEFT JOIN comment_field_data AS child ON parent.cid = child.pid
INNER JOIN users_field_data as user ON parent.uid=user.uid
INNER JOIN node_field_data as node_field ON parent.entity_id=node_field.nid
GROUP BY parent.cid
ORDER BY COUNT(child.cid) DESC;



With Subselect:

SELECT cfd.cid, top_list.reply_count, ufd.uid, ufd.name
FROM (
  SELECT tl.pid, COUNT(tl.pid) AS reply_count 
  FROM comment_field_data tl WHERE tl.pid IS NOT NULL
  GROUP BY tl.pid
) top_list
INNER JOIN comment_field_data cfd ON top_list.pid = cfd.cid
INNER JOIN users_field_data ufd ON cfd.uid = ufd.uid
ORDER BY top_list.reply_count DESC, cfd.created DESC
LIMIT 0, 10;



With Subselect and Drupal:

// Inner select: Get comments with the most direct replies.
$top_list_inner =  $connection->select('comment_field_data', 'tl')
  ->fields('tl', ['pid'])
  ->isNotNull('tl.pid')
  ->groupBy('pid');
$top_list_inner->addExpression('COUNT(tl.pid)', 'reply_count');

// Outer select: Get the ID and name of the comments.
$top_list_with_users = $connection->select($top_list_inner, 'top_list');
// Join the comment table to get the ID of the comments authors...
$top_list_with_users->innerJoin('comment_field_data', 'cfd', 'cfd.cid = top_list.pid');
// ...Then we can get their name!
$top_list_with_users->innerJoin('user_field_data', 'ufd', 'cfd.uid = ufd.uid');
$top_list_with_users
  ->fields('cfd', ['cid'])
  ->fields('top_list', ['reply_count'])
  ->fields('ufd', ['uid', 'name'])
  ->orderBy('top_list.reply_count', 'DESC')
  ->orderBy('cfd.created', 'DESC');