[jherdman]

Composability is the often cited benefit. As an example, I can do the following in Active Record (Ruby):

  class Account < ApplicationRecord
    scope :active, -> { where.not(active_at: nil) }

    belongs_to :user
  end

  class User < ApplicationRecord
    scope :born_before, ->(born_on) { where(birthdate: born_on) }
  end

  active_users_with_birthdays_today = Account.active.joins(:user).merge(User.born_before(Date.today))

Contrived, of course, but it's not hard to see how you can use these sorts of things to build up record sorting, filtering, etc. Doing this by hand wouldn't be very fun.

[sgarland]

The thought process can be the same in SQL. You can start by writing SELECT * FROM Account; then add your JOIN to User, then add your predicates. Then you can refine it – here, if I’m understanding the code correctly, you’re using User for a JOIN but never return anything from that table, so you could turn it into a semi-join (WHERE EXISTS) and likely get a speed-up.

[iterateoften]

> then add your predicates

How are you canonically storing these predicates in code to reuse over 5-10 queries?

[cess11]

Why would you want to?

[iterateoften]

The fundamentals of software design. DRY

If you have a complex predicate that defines a set of rows, and you use that set in many different queries, are you rewriting it each time?

[cess11]

So you don't actually have an answer for me?

Maybe I do, maybe I don't. It depends. Most likely I won't, unless it's used in like 80 % or more of queries and the problem domain guarantees that it will basically never change and if it does it will for sure, absolutely, change everywhere at once.

I like easily testable SQL queries that are easy to read, right there in the code. No need to execute anything to get to the SQL that would execute in the database. 'But then I need to write thousands of queries?!' Yes, you do, and you'll have a chill and easy time whenever they change, and they will if your software gets use and your data grows. Because you can just search/replace on the SQL text, and don't need to figure out every place some mutilated piece might get called from and probably get it wrong a few times or need to figure out how to split it into two or more fragments.

In some cases you can figure out a robust way to generate SQL but it's rare, it mostly comes up in one-off scenarios.

[Eikon]

I completely agree. People often bring up edge cases like this, but they rarely occur in real-world scenarios. If you have such a complex predicate that’s used everywhere, you should probably be using a view anyway.

[Eikon]

I'm not sure why you think you’d need an ORM for that. Most SQL client libraries allow you to compose queries, and query builders, which are not ORMs, can handle that just fine too.

[iterateoften]

By the time you have a query builder that will rewrite queries so that column name and table names to be compatible with composition you basically have an ORM.

Especially if you are defining types anyways to extract the data from the sql into.