> I realize you can do complicated JOINs, but isn't that almost as bad from a performance perspective?
No, JOINs should be orders of magnitude faster.
> What are you really supposed to do if you need to, e.g. fetch a list of posts along with the number of comments on each post?
You're really supposed to do a JOIN, together with a GROUP BY and a COUNT(). This is elementary SQL.
it gets more complicated when you need to also display something like "last comment: <author> <3 days ago>" for each post, or if the comment counts need to be filtered by various flags/states/etc.
of course, it's all possible with custom SQL but it gets complicated quick.
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.