Method: OccamsRecord.sql

Defined in:
lib/occams-record/raw_query.rb

.sql(sql, binds, use: nil, query_logger: nil) ⇒ OccamsRecord::RawQuery

Starts building a OccamsRecord::RawQuery. Pass it a raw SQL statement, optionally followed by a Hash or Array of binds. While this doesn’t offer an additional performance boost, it’s a nice way to write safe, complicated SQL by hand while also supporting eager loading.

results = OccamsRecord.sql("
  SELECT * FROM widgets
  WHERE category_id = %{cat_id}
", {
  cat_id: 5
}).run

If you want to do eager loading, you must first the define a model to pull the associations from (unless you’re using the raw SQL eager loaders ‘eager_load_one` or `eager_load_many`).

results = OccamsRecord
  .sql("
    SELECT * FROM widgets
    WHERE category_id IN (%{cat_ids})
  ", {
    cat_ids: [5, 10]
  })
  .model(Widget)
  .eager_load(:category)
  .run

NOTE To use find_each/find_in_batches, your SQL string must include ‘LIMIT %batch_limit OFFSET %batch_offset’, and an ORDER BY is strongly recomended. OccamsRecord will provide the bind values for you.

NOTE There is variation of the types of values returned (e.g. a Date object vs a date string) depending on the database and ActiveRecord version being used:

Postgres always returns native Ruby types.

SQLite will return native types for the following: integers, floats, string/text. For booleans it will return 0|1 for AR 6+, and “t|f” for AR 5-. Dates and times will be ISO8601 formatted strings. It is possible to coerce the SQLite adapter into returning native types for everything IF they’re columns of a table that you have an AR model for. e.g. if you’re selecting from the widgets, table: ‘OccamsRecord.sql(“…”).model(Widget)…`.

MySQL Mostly native Ruby types, but more testing is needed.

Parameters:

  • sql (String) —

    The SELECT statement to run. Binds may be Rails-style (?, :foo) or Ruby-style (%s, %foo).

  • binds (Hash) —

    Bind values as Hash (with Symbol keys) or an Array

  • use (Array<Module>) (defaults to: nil) —

    optional Module to include in the result class (single or array)

  • query_logger (Array) (defaults to: nil) —

    (optional) an array into which all queries will be inserted for logging/debug purposes

Returns:



50
51
52
 
# File 'lib/occams-record/raw_query.rb', line 50

def self.sql(sql, binds, use: nil, query_logger: nil)
  RawQuery.new(sql, binds, use: use, query_logger: nil)
end