3 ms·
> 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
by crazygringo 1y ago
> 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.
- joevandyk 1y agoit 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.
- crazygringo 1y agoThat'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.
- joevandyk 1y agoYes, all that’s possible. But it’s not straightforward in the majority of ORMs.
- crazygringo 1y agoRight, 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.
- Valodim 1y agoAlmost sounds like ORMs are a bad idea