HAVING filters produced rows
Questions?
ORM-wise it's not a huge issue, I tend to move them outside the query definition to avoid the duplication. E.g.
comment_count = func.count(Comment.id).label('comment_count')
query = (session.query(User, comment_count)
.join(User.comments)
.group_by(User)
.having(comment_count > 10))SQL is like democracy: it's the worst form, except for every other.
Go try writing non-trivial queries in any other database query language, then come back and whine to me about SQL.
Notably, SQL hides the implementation in a way that allows for 1000+x speedups from index selection, JOIN order optimization, execution method and parallelism. Typically, even hand-coded queries can't touch the performance because of gonzo low level optimizations nobody would bother writing for one query, such as optimizing batch size to match cache size.
Modern SQL (e.g. Postgres) even does this while allowing the user to include unmodified business logic written in a variety of high level languages. For example, if you want to search for employees that match some crazy predicate written in (JavaScript/Python/etc) no problem, it fits right into the WHERE clause, and is executed in parallel on each core of each node. You can even index the results of this function, so the storage system doesn't even look at non-matching records... assuming this index is the more selective of the options, which it dynamically decides per query.
tl;dr: quit your whining
Which query languages (other than ORMs) are the top contenders when staying within the rational model to compare with?
While there are notable examples of non-relational databases that use SQL as their native query language, I was recently looking for a relational database that natively uses a language other than SQL and came up short. I imagine there is something out there, but SQL dominates the landscape.
I am familiar with a few alternative languages, some of which compile to SQL. But it is pretty bleak out there, which is interesting given how many general purpose programming languages we have. I expect because ORMs have become the language people use to query databases, all of the attention have gone into them, rather than improving native query languages.