That's like saying it gets more complicated when you have to use loops with break statements in programming. It's just what programming is.
The filtering you describe is trivial with COUNT(flag IN (...) AND state=...) etc.
If you want to retrieve data on the last comment, as opposed to an aggregate function of all comments, you can do that with window functions (or with JOIN LATERAL for something idiomatic specifically to Postgres).
Learning how to do JOIN's in SQL is like learning pointers in C -- in that it's a basic building block of the language. Learning window functions is like learning loops. These are just programming basics.
Yes, all that’s possible. But it’s not straightforward in the majority of ORMs.
Almost sounds like ORMs are a bad idea
Right, and ORMs are the main cause of the N+1 problem, and atrocious database performance in general.
For anything that isn't just a basic row lookup from a single table, you should really just be writing the SQL yourself.