[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?

[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.