SQL just isn't composable. I know this article is old, but these days it's not black and white. In the space between ORM and raw SQL there are things like AREL which can save a ton of dev effort without the "impedance mismatch".
Composing in this context means that one part of your application (eg the list controller) builds part of a query (the select from) and another part (eg the filter controller) builds another part of the query (the where part) and yet another part of you application (the paginator) alters there where part, adds limits and offset and build another query based on the same filter conditions to calculate the total row count. All that without the different controllers knowing each others in advance. This is not possible without complicated string manipulation or building some not-sql-query-algebra.
Some api like prepared statements (supported by the db itself) for building up complex queries step by step would be nice. Does something like this exist?
As the other answer to you says: subqueries will do that. DBs automagically unnesting subqueries has been a thing for a long time, so usually you won't get any performance impact (With a big warning for correlated subqueries).
Neat, not having used rails ever, I didn't know about Arel. Speaking about composable sql, I've had some good success with dbt lately. It has a feature where it lets you 'compile' a query out of multiple select models. Now I wonder whether it'd be feasible to use that dynamically, within the context of an application...
