StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POCounting comments with one level of nesting
text
Body
copied!<p>I've got a comment system in which users can comment on posts, and then users can reply to those top-level comments. That's the extend of the nesting: users cannot reply to replies.</p> <p>Top-level comments and replies live in the same table. They're almost identical. Top-level comments have a <code>post_id</code>, and replies have a <code>parent_id</code>. I use a constraint on the table to ensure that exactly one of these columns has a value for each row.</p> <pre><code>CREATE TABLE comments ( id integer NOT NULL, post_id integer, author_id integer NOT NULL, body text, created_at timestamp without time zone, updated_at timestamp without time zone, parent_id integer, CONSTRAINT must_have_media_item_xor_parent CHECK ((((media_item_id IS NULL) AND (parent_id IS NOT NULL)) OR ((media_item_id IS NOT NULL) AND (parent_id IS NULL)))) ); </code></pre> <p>Now I'd like to count all of the comments, including replies, on a particular post. The query</p> <pre><code>SELECT count(*) FROM comments WHERE comments.post_id = 123 </code></pre> <p>tells me how many top-level comments post #123 has. The query</p> <pre><code>SELECT count(*) FROM comments JOIN comments AS replies ON replies.parent_id = comments.id WHERE comments.post_id = 123 </code></pre> <p>tells me how many replies there are. I could do both and add them together, but that sounds heavy, and hopefully unnecessary.</p> <p>One solution, which would avoid the self join from the second query, would be to set the <code>post_id</code> on replies as well, denormalizing the value. Then the first query would count all of them. If I do that, I'd really like to have some sort of consistency check in the database to make sure I have it right.</p> <p>Is there a way to get Postgres to constrain the value of <code>post_id</code> to that of its parent when <code>parent_id</code> is set? Normal constraints appear to only be able to look at a single row.</p> <p>Or, is there another good way to count all the comments in one fell swoop?</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload