I have a website on which users can place comments on nodes and also comment as a reply to other comments without a depth limit. The table structure of the comments table is as follows (simplified):
| cid | entity_type | entity_id | comment |
|---|---|---|---|
| 1 | node | 1 | initial comment on node with id 1 |
| 2 | comment | 1 | reply to comment with id 1 |
| 3 | comment | 2 | reply to comment with id 2 |
| 4 | comment | 1 | second reply on first comment |
| 5 | node | 2 | comment on another node |
| 6 | node | 1 | second direct child comment on node 1 |
| 7 | node | 3 | comment on a third node |
The nodes themselves are not part of this table. The visualized hierarchical structure of the data above is
node with id 1
|- (cid: 1) initial comment on node with id 1
| |- (cid: 2) reply to comment with id 1
| | |- (cid: 3) reply to comment with id 2
| |- (cid: 4) second reply on first comment
|- (cid: 6) second direct child comment on node 1
node with id 2
|- (cid: 5) comment on another node
node with id 3
|- (cid: 7) comment on a third node
So, effectively, the columns “entity_type” and “entity_id” in unison identify the entity that the comment belongs to.
Now I would like to run a sql query that returns me the node ids (!) and the number on comments on these nodes of nodes which ids are given – so something like this, when only nodes with the id 1 and 2 are of interest:
| node | # comments |
|---|---|
| 1 | 4 |
| 2 | 1 |
I tried multiple versions of recursive queries (all using the “with recursive x as…” syntax), but for the life of me I am seemingly unable to form the correct one. Any help would be greatly appreciated…
Thanks for taking the time to read and have a great day!