Duplicate rows, NULLs, ordering, etc. But there is no distinction to be made here, just calling attention to your grievous error so you don't make it again. We don't want to look like a fool again next time. Viva la education!

> I'm also not sure how to answer your question.

You put a lot of effort into a rather detailed response not once, but twice. You obviously know how to answer the question at a technical level, but perhaps you don't understand the nuance of my question? What I mean is: Show us some example code that demonstrates query builder composition and the pure SQL that you would use to replace it.

I'm not sure what you have in mind either for query builders or their composition. Like I said, some builders are really just wrappers to rename SQL operations and have a method chaining syntax. Those are always going to compile to obvious, analogous sql (e.g. `Users.filter(_.id==id).map(_.name)` will compile to `select name from users where id=?`. For the most part I think these are fine but maybe redundant. Then there are ORMs that do a bunch of state tracking and might produce multiple statements from one expression. These are usually what people get opinionated about. What's an example of query builder composition that you think can't be written in SQL?

The T in CTE literally stands for table. Even if you're right about the others, how could someone not think of that one as being a table? Regardless, now they can! Isn't education a wonderful thing?

> postgresql documentation uses

In fairness, Postgres originally implemented QUEL, which is relational. It didn't move to SQL until quite late in its life. It often takes longer to update documentation than to write code, especially in open source projects where the contributors tend to much prefer writing code over updating documentation.

> and have a method chaining syntax.

And this is often how composition is implemented. Not strictly so, but a common way to do it. Consider:

    users = select("*").from("users")
    admin_users = users.where("role = 'admin'")
    recent_admin_users = admin_users.where("created > LAST_WEEK()")

Of course, you could do the obvious and write out 100 completely separate almost identical SQL queries, but that's not exactly maintainable and it's not the 1940s anymore. You are going to quickly hate everything about your existence as soon as those queries need to change. This is the reason people turn to query builders. If you only had to ever write one or two queries there'd be no point, but that never happens within the domain where these tools are used.

But perhaps there is a better way. This is where you would give us example code to show how you would replace that query builder code with a pure SQL solution.

    create view admin_users as select * from users where role='admin';
    create view recent_admin_users as select * from admin_users where created > LAST_WEEK();

I wouldn't use views for something so small, but I probably wouldn't use a query builder either. If you want a stable API, make a view to indirect access to the table(s). Don't break your view API. If you change the underlying table, update the view to keep it as a stable interface.

Query builders can be nice for generic code. E.g. you have a variable length list of predicates (e.g. from http query strings) and want to do `predicates.fold(_ => true)(_ and _)`. In that case you're basically using it as a macro system, which works because sql fragments compose. In fact IMO the most pleasant way to use a query builder is usually exactly as string interpolation macros.

ORMs, the original topic at hand, are an entirely different beast. I think generally people who bash ORMs don't have much issue with query builders.

That gets you halfway there, perhaps, but I'm not sure it explains how you would use it in an actual application. Are you back to writing hundreds of "SELECT * FROM recent_admin_users"-type queries in the application, once again exploding the development effort and maintenance surface – exactly what you're trying to avoid with these types of tools?

> ORMs, the original topic at hand, are an entirely different beast.

The original topic at hand is active record, not ORM. ORM is, as the name literally tells, about mapping between relations (or, in practice, tables) and objects. No sane person is going to bash ORM. They may dislike a particular toolkit that tries to help with ORM, but at very least they are going to do ORM by hand.

But that's not what we're talking about. The original comment that set the context for this particular discussion is about query building. It literally proposed using an LLM to generate queries instead. You can query build in the active record style: e.g. `User::all().admins().latest()`, but that's still query building, exactly like the earlier example except with a different API.