Discussion on: ORM vs. SQL?

[MetaDave 🇪🇺]

Sure – I think that scopes are a good example.

The ability to define something as simple as:

scope :forthcoming_or_active, -> {
  where(publishing_status: [Book::PUB_STATUS_FORTHCOMING, Book::PUB_STATUS_ACTIVE])
}

scope :publishing_within_months, ->(months) {
  forthcoming_or_active.
    pub_date_between(
      Time.zone.today,
      Time.zone.today + months.to_i.months
    )
}
scope :not_ebook, -> {
  where.not(product_form: Book::PRODUCT_FORM_EBOOK)
}
scope :sales_present, -> {
  where(Sale.belonging_to_book.exists)
}

So these define class methods that I can daisy chain in the application to be able to identify the records I want:

• Book.not_ebook
• Book.sales_present.not_ebook
• Book.not_ebook.sales_present.publishing_within_months(1)

These all generate the correct SQL, and if I change the definition of not_ebook in the scope then every SQL is automatically changed to the new definition. There are 18 places in our system where #not_ebook is used, and not only would I not want to correct them all by hand, I would not fancy my chances of being sure that I can find them all.

So that's pretty hard to do with SQL, efficiently at least. You could conceive of it being done with a PL./SQL function in Oracle perhaps, but when you stray from executing pure SQL there's the risk of compromising performance.