Skip to content

Instantly share code, notes, and snippets.

@nevergone
Last active February 17, 2023 07:23
Show Gist options
  • Save nevergone/526978ca7db21a0c2098f51894eb3445 to your computer and use it in GitHub Desktop.
Save nevergone/526978ca7db21a0c2098f51894eb3445 to your computer and use it in GitHub Desktop.
Posts with the most one-level children comments (Drupal 8-10)
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');
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment